Combine 2 Worksheet_Change Events

Claymationator

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
I have two separate drop-downs in one sheet, and when either of them is "Total" I need to run a different set of code. I have each working individually, but can't have the same macro name on one sheet. Any help combining the two?

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range("D7").Value
        Case "Total"
        Call OrderTypeFilter
    End Select
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range("D8").Value
        Case "Total"
        Call RxTypeFilter
    End Select
    Application.EnableEvents = True
End Sub

Any help is appreciated.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range("D7").Value
        Case "Total"
        Call OrderTypeFilter
    End Select
    Select Case Range("D8").Value
        Case "Total"
        Call RxTypeFilter
    End Select
    Application.EnableEvents = True
End Sub
 
Upvote 0
Thanks for the quick post, it seems to be working just fine.

However, there are a few other drop-downs on the same sheet, and the code also runs whenever I change anything else on the sheet. Is there a way to make sure that it only runs when D7 or D8 is changed to "Total"?
 
Upvote 0
Like so:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

    For Each cell In Target
        If Not Intersect(cell, Range("D7:D8")) Is Nothing Then
            Application.EnableEvents = False
            Select Case cell.Address
                Case "$D$7"
                    If cell.Value = "Total" Then Call OrderTypeFilter
                Case "$D$8"
                    If cell.Value = "Total" Then Call RxTypeFilter
            End Select
            Application.EnableEvents = True
        End If
    Next cell

End Sub
 
Upvote 0
No need to do a For Each loop here:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(target, Range("D7:D8")) Is Nothing Then
   Application.EnableEvents = False
   Select Case target.Address
      Case "$D$7"
          If cell.Value = "Total" Then Call OrderTypeFilter
      Case "$D$8"
          If cell.Value = "Total" Then Call RxTypeFilter
   End Select
   Application.EnableEvents = True
End If

End Sub
 
Upvote 0
I have tried both sets of code listed above, and neither of them want to work for me. I can change either dropdown to "Total" and nothing happens.
 
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D7:D8")) Is Nothing Then
   Application.EnableEvents = False
   Select Case Target.Address
      Case "$D$7"
          If [B][COLOR=red]Target[/COLOR][/B].Value = "Total" Then Call OrderTypeFilter
      Case "$D$8"
          If [B][COLOR=red]Target[/COLOR][/B].Value = "Total" Then Call RxTypeFilter
   End Select
   Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Still nothing even after changing that. The sheet updates just fine if other options are selected in the drop-downs, but when I select "Total" nothing happens. I have run each individual code (RxTypeFilter and OrderTypeFilter) separately and they work fine, but they just aren't being triggered on the worksheet change.
 
Upvote 0
There is one more thing that I found that I need to add to this portion of the code. Basically I have a named cell called "TotalFilter" where I have concatenated D7 & D8, and I don't want the Worksheet_Change event to happen if that cell equals "TotalTotal".

So basically I need to check for that first, and if that cell equals "TotalTotal" then don't run the procedure, but if the user selects anything else, then I want it to run just as it is currently written.

I hope that makes sense.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Disable Worksheet Change Event
2 3
Replies
21
Views
889
kevin9999
K
O
  • Solved
Combining two VBA Codes Change (ByVal Target) and (ByVal Destination)
2
Replies
10
Views
959
OscarMDMBA
O
H
  • Question
Error 1004: Copy method of Range class failed
Replies
2
Views
275
Alex Blakenburg
A
S
  • Solved
Keep All Caps when event code changes it to Proper Case
Replies
7
Views
346
Joe4
Joe4
J
  • Question
Merge 2 VBA code help
Replies
1
Views
471
Joe4
Joe4

Forum statistics

Threads
1,215,329
Messages
6,124,302
Members
449,149
Latest member
mwdbActuary

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top