cal macro

[needhelp2]

Hi,

I want to know that how can i call macro to run automatically when a value select from drop down menu.
Like i have drop down menu in which i have name smith
i had made macro that when i select amazon when i copy some values from other sheets and place it under smith in same sheet where smith is selected

But i am facing issue when ever i select smith macro wont run automatically. i want that how macro run automatically when i select smith.matcros for smith has been saved with the name of smith

Thanks

 

[hatstand]

What kind of drop down are you using. Validation, Filter, Form control?

 

[Sektor]

Use Application.Run method and pass it macro name. For example, in Worksheet_Change event check every time whether cell with macro name has been changed. If so, then call Application.Run.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Run Range("A1") 'Let A1 hold text "RunSub"
End Sub

Sub RunSub()
    MsgBox "I was executed!"
End Sub

 

[hatstand]

I would go for this. But if your using a filter it won't run the change event as it doesn't recognise the cell as being changed. Use a validation or a form tool to poulate a cell that will make the change event run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call YourCode
End If
End Sub

Sub YourCode()
MsgBox "I was executed!"
End Sub

 

[Sektor]

You can add DropDown control programmatically and select values from there to run macro (when double-click cell).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Sub FillDropDown()

    Dim dd As DropDown

    With ActiveCell
        
        ' Add drop down control into active cell.
        Set dd = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
    
    With dd
        .DropDownLines = 30
        .Name = "dd1"
        
        ' Macro to run
        .OnAction = "RunSub"
        
        ' Add items. Use any source. Can also use List property as Combobox.
        .AddItem "Smith"
        .AddItem "John"
        
    End With

End Sub

Sub RunSub()

    With ActiveSheet.DropDowns(Application.Caller)
        Application.Run .List(.ListIndex) 'This gives selected value and pass to Run.
        .Delete
    End With

End Sub

 

[Sektor]

In previos code remove line "Sub FillDropDown()".

 

[needhelp2]

In previos code remove line "Sub FillDropDown()".

thanx buddy. Its really work as i need