Drop down box value to trigger macro

willow1985

Active Member
Joined
Jul 24, 2019
Messages
443
Office Version
2019
Platform
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
305
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
305
Use Dante's or add is nothing after the intersect function and remove the not from mine.
 

willow1985

Active Member
Joined
Jul 24, 2019
Messages
443
Office Version
2019
Platform
Windows
Works perfectly, thank you so much! :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

TritonCoding

New Member
Joined
Apr 7, 2020
Messages
2
Office Version
365
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,410
Messages
5,414,242
Members
403,522
Latest member
Abel_excel

This Week's Hot Topics

Top