Drop down box value to trigger macro

willow1985

Active Member
Joined
Jul 24, 2019
Messages
424
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
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
292
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
9,770
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
292
Use Dante's or add is nothing after the intersect function and remove the not from mine.
 

DanteAmor

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

Forum statistics

Threads
1,086,206
Messages
5,388,423
Members
402,116
Latest member
Chaskon

Some videos you may like

This Week's Hot Topics

Top