Run a Macro when a dropdown list is changed

ikettle

New Member
Joined
Feb 25, 2020
Messages
3
Hi, I am trying to run a macro (which runs perfectly fine by itself, when a dropdown list is changed. Here is the code I have on the view code page

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$9" Then
If Range("E9").Value = "Promotion" Then
Rows("1:52").EntireRow.Hidden = False
Rows("53:77").EntireRow.Hidden = True
Rows("78:90").EntireRow.Hidden = False
ElseIf Range("E9").Value = "CRB" Then
Rows("37:67").EntireRow.Hidden = True
Rows("68:90").EntireRow.Hidden = False
ElseIf Range("E9").Value = "Off Cycle Salary Increase" Then
Rows("78:90").EntireRow.Hidden = False
Rows("53:68").EntireRow.Hidden = False
Rows("37:52").EntireRow.Hidden = True
Rows("69:75").EntireRow.Hidden = True
End If
End If
End Sub

Private Sub Worksheet_Change1(ByVal Target As Range)
If Not Intersect(Target, Range("E56:G56")) Is Nothing Then
Application.Run "searchdata3"
End If
End Sub


The second dropdown list highlighted in red, is what is not working for me. Does anyone know what I need to change in the above to have the macro run, when the dropdown list is changed?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Worksheet_Change1 is not a valid procedure name, all of the code needs to be in the first procedure.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$9" Then
    ' stuff to do
ElseIF Not Intersect(Target, Range("E56:G56")) Is Nothing Then
    ' stuff to do
End If
End Sub
Please remember to use code tags when posting code by clicking the </> icon, then pasting your code into the popup window.
 
Upvote 0
Thank you for your reply. I have done this to the code but nothing happens when I change the dropdown list in G56

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$9" Then
        If Range("E9").Value = "Promotion" Then
            Rows("1:52").EntireRow.Hidden = False
            Rows("53:77").EntireRow.Hidden = True
            Rows("78:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "CRB" Then
            Rows("37:67").EntireRow.Hidden = True
            Rows("68:90").EntireRow.Hidden = False
         ElseIf Range("E9").Value = "Off Cycle Salary Increase" Then
            Rows("78:90").EntireRow.Hidden = False
            Rows("53:68").EntireRow.Hidden = False
            Rows("37:52").EntireRow.Hidden = True
            Rows("69:75").EntireRow.Hidden = True
        ElseIf Not Intersect(Target, Range("E56:G56")) Is Nothing Then
            Application.Run "searchdata3"
        End If
    End If
End Sub
 
Upvote 0
Very close, you have 2 block If's and you had it set to the wrong one, the block of code that works when E9 is changed needs to end before checking the next range for a change.
The correct way would be
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$9" Then
        If Range("E9").Value = "Promotion" Then
            Rows("1:52").EntireRow.Hidden = False
            Rows("53:77").EntireRow.Hidden = True
            Rows("78:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "CRB" Then
            Rows("37:67").EntireRow.Hidden = True
            Rows("68:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "Off Cycle Salary Increase" Then
            Rows("78:90").EntireRow.Hidden = False
            Rows("53:68").EntireRow.Hidden = False
            Rows("37:52").EntireRow.Hidden = True
            Rows("69:75").EntireRow.Hidden = True
        End If
    ElseIf Not Intersect(Target, Range("E56:G56")) Is Nothing Then
        Application.Run "searchdata3"
    End If
End Sub
 
Upvote 0
Thank you so much that did work.....But I am now trying to add another dropdown, but it does not like it :)

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$9" Then
        If Range("E9").Value = "Promotion" Then
            Rows("1:52").EntireRow.Hidden = False
            Rows("53:77").EntireRow.Hidden = True
            Rows("78:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "CRB" Then
            Rows("37:67").EntireRow.Hidden = True
            Rows("68:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "Off Cycle Salary Increase" Then
            Rows("78:90").EntireRow.Hidden = False
            Rows("53:68").EntireRow.Hidden = False
            Rows("37:52").EntireRow.Hidden = True
            Rows("69:75").EntireRow.Hidden = True
        End If
    ElseIf Not Intersect(Target, Range("E56:G56")) Is Nothing Then
        Application.Run "searchdata3"
    End If
    ElseIf Not Intersect(Target, Range("E40:G40")) Is Nothing Then
        Application.Run "searchdata4"
    End If
End Sub

What is missing or needs to be added here for the last ElseIf to work?
 
Upvote 0
Nothing needs to be added, this time you have too many End If's :oops:
I've added some comments to show where each If starts and ends, the new ElseIf is being added to the chain of the first if, so it needs to go in before the first If is ended.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$9" Then 'First if starts'
        If Range("E9").Value = "Promotion" Then ' Second if starts'
            Rows("1:52").EntireRow.Hidden = False
            Rows("53:77").EntireRow.Hidden = True
            Rows("78:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "CRB" Then
            Rows("37:67").EntireRow.Hidden = True
            Rows("68:90").EntireRow.Hidden = False
        ElseIf Range("E9").Value = "Off Cycle Salary Increase" Then
            Rows("78:90").EntireRow.Hidden = False
            Rows("53:68").EntireRow.Hidden = False
            Rows("37:52").EntireRow.Hidden = True
            Rows("69:75").EntireRow.Hidden = True
        End If 'Second if ends'
    ElseIf Not Intersect(Target, Range("E56:G56")) Is Nothing Then
        Application.Run "searchdata3"
    ElseIf Not Intersect(Target, Range("E40:G40")) Is Nothing Then
        Application.Run "searchdata4"
    End If ' First if ends'
End Sub

You only really need to end one if before starting another if it is possible that both could be true under some, but not all conditions.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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