[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Excel 2007 Macro that fires when specific cell changes

Hello,

Can someone assistance in putting a VB code together that automatically triggers a macro when a specific cell changes?  For example,  if the data in cell D8 on sheet1 changes, the macro 'autofilter1' is ran.

Thanks!
0
Escanaba
Asked:
Escanaba
  • 4
  • 4
  • 3
  • +1
3 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

Put this code into the Sheet module (right click the sheet tab, select View Code and paste the code into the big code area)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D8")) Is Nothing Then
    On Error Resume Next
    Application.EnableEvents = False
    call autofilter1
    Application.EnableEvents = True
End If
End Sub

Open in new window


cheers, teylyn
0
 
jppintoCommented:

Sub Worksheet_Change(ByVal Target As Range)
    
    Dim WatchRange As Range

    Set WatchRange = Range("D8")
    If Not Intersect(Target, WatchRange) Is Nothing Then
        'your code here
    End If

End Sub

Open in new window

0
 
broro183Commented:
hi,

Right click on the sheet tab in Excel & choose "view code" then paste this code in...
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Or Target.Address <> "$D$8" Then
            'do nothing
        Else
            Call autofilter1
        End If
End Sub

Open in new window


hth
Rob
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
broro183Commented:
*sigh* I'm too slow yet again!

Teylyn,
I like the way you have wrapped the action code inside the "Application.EnableEvents" statements (I forgot!), but I'm curious, why have you placed the "on error resume next" within the If statement?

Escanaba,
I only saw the other solutions after posting mine, & personally, I do prefer their use of "Intersect" over my use of "target.address". However, you may (or may not, esp in your specific example) find it useful to also include my check that cell D8 is the only one being changed (ie "Target.Cells.Count > 1").

Rob
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Rob, I may be wrong, but I thought to use the on error only when the rest of the code fires. And I put it there so that the Application.EnableEvents = True gets fired to return Excel to the normal state.
0
 
EscanabaAuthor Commented:
teylyn,

When I run your code I get the following error:
Compile Error
Expected Function or Variable

Any advice?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D8")) Is Nothing Then
    On Error Resume Next
    Application.EnableEvents = False
    call autofilter1
    Application.EnableEvents = True
End If
End Sub

Open in new window

0
 
jppintoCommented:
Did you tryed my code? I see you commenting on other's solution but none about mine!
0
 
broro183Commented:
hi everyone,

Escanaba,
re your compile error with Teylyn's code:
What line of code is highlighted when the error occurs?
Is your "'autofilter1' macro" in the same excel file as the worksheet?
If not, you need to provide the full name & path of the macro as discussed here.
Have you pasted the provided code into the Sheet Module & is your "'autofilter1' macro" in a standard module?

Teylyn,
Ahhh, yes, I can follow your logic. I just asked because I've had some odd experiences with Intersect in the past (see my historical comment to Paul in this EF post: http://www.excelforum.com/1878392-post4.html).
btw, I may be wrong but it looks like you've posted exactly the same code in your latest post...?

I think you are right*, although it's definitely a tradeoff isn't it, between masking errors & ensuring a "graceful exit"?
I'll suggest to the OP that the error handling should be wrapped as closely as possible around the likely problem areas within the 'autofilter1' macro.
*esp after a little test to remind myself of how errors are passed back up the Call Stack...
'in the sheet module
Option explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Stop
            Call RemindingMyselfAboutBehaviourOfErrors
            Debug.Print Err.Number
End Sub

'in a normal module
Sub RemindingMyselfAboutBehaviourOfErrors()
    Err.Raise (1004)
    Debug.Print Err.Number
End Sub

Open in new window


Jppinto,
I'm keen on learning from/understanding other people's coding habits & I am curious, why don't you include "Set WatchRange = nothing" at the end of your code?


Rob
0
 
jppintoCommented:
Yes, you're right, I could put Set WatchRange = nothing at the end of the coding. I forgot...sorry!

But, did you tryed my code? Does that work for you?
0
 
EscanabaAuthor Commented:
jppinto - When I receive multiple solutions I try running all of them to not only ensure they work but also to test how quickly the code can perform the action.  I did try your code out and it did work as well as the code broro183 has provided.  I'll test out Teylyn's code this morning now that I have her update and assign points accordingly.

broro183 - call autofilter1 was the line of code causing the issues.  The code is in the same workbook and it was carried over into the sheet module.
0
 
EscanabaAuthor Commented:
Overall codes work fine.  Rob's code, based on the data I'm using, ran on average 3.5 seconds faster than the other two with Teylyn coming in 2nd.  Additional points to Teylyn for first response.  Appreciate everyones assistance and hope the point split seems fair.  Its always a challenge on this end of a solution to know how best to award points for multiple solutions.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the points. I'm glad to see that you picked the best solution as the answer. If you go with broro183, you're in for a well-tested, reliable solution. Good choice.

cheers, teylyn
0
 
broro183Commented:
Hi Escanaba,

Yep - I'm pleased I'm not the person awarding points :)

Thanks for the points, although I would have no problems if you weighted them more for the others who were faster than me.

"3.5 seconds faster" surprises me slightly, I think they should all be very close & that's a frustrating length of time (see link) for a worksheet change macro. If you want us to look at your other code feel free to ... (I'm not sure if it's acceptable for you to post it in this thread... or if you need to ask a new question), either way, I'm happy to look at the other code if you provide a link.

thanks Teylyn - I try :-)

Jppinto, no need to apologise, as I said I'm just interested to hear the reasons as some people are emphatic that there is no need to do it. Personally, I'm happy to add an extra line of code as it may save memory leaks etc (call me a cargo-cult or voodoo scaredy cat if you want!) ;)

Rob
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now