Data Validation Drop Down List To Trigger Macro

[pure vito]

Hi All,

Hopefully just a quick one

My aim here is to use "C3" dropdown selection to trigger a macro based on the value, I have used Data Validation to populate the dropdown,

I have a working VBA that when text is entered manually into "C3" it runs the macro and it works fine but I can't seem to get the same result with the dropdown list,

I have been looking online for a solution but have just been coming across the same VBA,

Thanks in advance,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        Select Case Range("C3")
            Case "Base Set Series": Baseset
           
        End Select
    End If
End Sub

 

[Peter_SSs]

Code works for me.
Make sure that you have that worksheet change code in the sheet module of the sheet with the drop-down, not in a standard module & not in the ThisWorkbook module.

 

[pure vito]

Hey Peter,

Thank you, I do have this code in the sheet module,

But I'm making the selection using the dropdown and it's just not triggering I'm stumped

The Dropdown is populated from a list in another sheet

 

[Peter_SSs]

Firstly, close right out of Excel and open up again in case your 'events' have become disabled.
Try the drop-down again.

If still no luck then add these two lines of code and choose the "Base Set Series" drop-down value again. If the first message box returns False, then perhaps the second one will shed some light on the issue.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        MsgBox Range("C3").Value = "Base Set Series"
        MsgBox "|" & Range("C3").Value & "|"
        Select Case Range("C3")
            Case "Base Set Series": Baseset
           
        End Select
    End If
End Sub

 

[James006]

Hi,

Just make sure ...
Application.EnableEvents = True

 

[pure vito]

Thank you guy's for your assistance,

@Peter_SSs I closed the workbook and tried again with no luck, I then tried the code you have provided here, unfortunately this was the result so does this setup work for you Peter are you able to trigger a macro using a dropdown selection?

Hey @James006 thanks for dropping in, I also tried to include the line you have provided and still the same result I did try this previously also

 

[Peter_SSs]

That message box result means that the value in C3 (the drop-down cell) is not identical to the typed value "Base Set Series"
Can you show what the second message box showed?

Check for leading/trailing/multiple internal spaces in both
Check that all "space" characters are normal space characters: CHAR(32)

For example, here I am checking the two space characters in C3

pure vito.xlsm
	C	D	E
3	Base Set Series	32	32
Sheet1

Cell Formulas
Range	Formula
D3	D3	=CODE(MID(C3,5,1))
E3	E3	=CODE(MID(C3,9,1))

 

[pure vito]

Apologies Guy's I have resolved the issue a space at the end of the text form the dropdown list was the issue had I tried another macro I might have resolved this issue sooner but I really appreciate you coming forward to help it's always appreciated, I thought I had exhausted all potential errors I just didn't see that space in the text I pasted the text from the code into the cell and seen the difference that way, Thanks again

 

[Peter_SSs]

Looks like we were both onto the same thing at the same time.

Check for leading/trailing/multiple internal spaces in both

a space at the end of the text form the dropdown list was the issue

 

[pure vito]

Haha your spot on thank you @Peter_SSs you'd have got me the result regardless