Select the Name in the Data Validation list on a cell value then the same name run the sub macro

[kpmsivaprakasam2003]

Excel VBA code is required to Select the Name in the Data Validation list on a cell value after the same name could be run in the sub macro.

E.g. Data Validation List assigned in "Sheet3" cell text value "B2". if I select the "AA" and then Call to "AA" Sub Marco run. I got error the code line "Application.Run Text".

Below Excel VBA code here: Excel version is 2013

Private Sub Worksheet_Change(ByVal Target As Range)
'Run Macro when Drop Down value selected
'VBA to run from Data Validation
'Auto Show Drop-Down List When Selecting the Cell
If Target.CountLarge > 1 Then Exit Sub

If Target.Address = Range("B2").Address Then
Application.EnableEvents = False

Dim Text As String
Text = ThisWorkbook.Sheets("Sheet3").Range("B2").Value
Application.Run Text

'or

'If Target.Address = "$A$1" Then
'Call ThisWorkbook.Sheets("Sheet3").Range("B2").Value
'End If
End If

Application.EnableEvents = True
End Sub


Public Sub AA()
MsgBox "AA Macro"
End Sub


Public Sub BB()
MsgBox "BB Macro"
End Sub


Public Sub CC()
MsgBox "CC Macro"
End Sub


Thanks for Advance
Sivapraakasam K

 

[DanteAmor]

You must remove the following code from the sheet code and put it in a module.

Public Sub AA()
MsgBox "AA Macro"
End Sub

Public Sub BB()
MsgBox "BB Macro"
End Sub

Public Sub CC()
MsgBox "CC Macro"
End Sub

Regards
Dante Amor

 

[kpmsivaprakasam2003]

You must remove the following code from the sheet code and put it in a module.

Public Sub AA()
MsgBox "AA Macro"
End Sub

Public Sub BB()
MsgBox "BB Macro"
End Sub

Public Sub CC()
MsgBox "CC Macro"
End Sub

Regards
Dante Amor

Thanks for reply, but, not working in a module

 

[kvsrinivasamurthy]

After transferring the code to module close the file with save, then reopen and test.

 

[RoryA]

A reminder

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Select the Name in the Data Validation list on a cell value then the same name run the sub macro - OzGrid Free Excel/VBA Help Forum

Excel VBA code is required to Select the Name in the Data Validation list on a cell value after the same name could be run in the sub macro. E.g. Data Validation List assigned in "Sheet3" cell text value "B2". if I select the "AA" and then Call to "AA"…

forum.ozgrid.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem. However, this is the third time you have done it, with no acknowledgement on any occasion; if you do it again, you will be banned.

 

[Fuji]

If it is written in a worksheet module,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "B2" Then Exit Sub
    If Target <> "" Then Run Me.CodeName & "." & Target
End Sub

 

[DanteAmor]

Thanks for reply, but, not working in a module

You can put an image to see how you have the code on the sheet and in the module.

It works well for me.