Drop down box value to trigger macro

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. Windows
I have drop down lists in column O where you can toggle the status from "blank", to "open" to "closed".

What I would like to do is have a certain macro run every time the status is changed in any of the cells in column O.

the following vba code that I currently have (that I inserted under sheet3 (Database)) is below but currently when I change any of the status of any of the cells in column O on the Database sheet nothing happens.

Could someone be able to tell me what I am missing?

Thank you :)


Code:
Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "O:O" Then Exit Sub
    
    Select Case Target.Text
    Case "Open": Call Open1
    Case "Closed": Call Closed
    Case Else: Exit Sub
End Select
End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I have drop down lists in column O where you can toggle the status from "blank", to "open" to "closed".

What I would like to do is have a certain macro run every time the status is changed in any of the cells in column O.

the following vba code that I currently have (that I inserted under sheet3 (Database)) is below but currently when I change any of the status of any of the cells in column O on the Database sheet nothing happens.

Could someone be able to tell me what I am missing?

Thank you :)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Columns("O")) Or Target.count > 1 Then Exit Sub
    
Select Case Target.Text
    Case "Open": Call Open1
    Case "Closed": Call closed
    Case Else: Exit Sub
End Select
    
End Sub
 
Last edited:
Upvote 0
You must compare data with data, address with address, column with column, ex:


Code:
  If Target.Column <> Columns("O").Column Then Exit Sub

Or

Code:
  If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
 
Upvote 0
Use Dante's or add is nothing after the intersect function and remove the not from mine.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Slight modification of the if intersect statement

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("E1")) Is Nothing Then
        Select Case Range("E1")
            Case "Phil": Macro1
            Case "Ralph": Macro2
            Case "Susan": Macro3
        End Select
    End If
End Sub

Video showing the code
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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