Drop down box value to trigger macro

willow1985

Board Regular
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
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
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top