automatically change assigned macro

cybergremlin

New Member
Joined
Dec 11, 2018
Messages
22
Hi

Is it possible to automatically change the macro that is assigned to an object?

If i create a list and i choose 1 of the options I would like the macro assigned to a button to then change? i know i can trigger this on the data validation but I could do with the next step in place instead.

thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I am not sure that I fully understand what you are trying to do or why, but would something like the following work instead?
Have your macro have some IF...THEN statements (or SELECT ... CASE) where it calls different macros to run based on the selection?
 
Upvote 0
i currently have 10+ buttons with different macos assigned to each of them
my idea was to only have 1 button with a drop down above this, so someone could then select the macro they wanted to run from the list and press the button

the reason i didn't want to run when the data validation was made is because someone might want to run the same macro twice in a row
 
Upvote 0
Why not have your button bring up a list form VBA, listing them 1-10, asking them to enter the proper number?

Here is a simple example, with 3 options to show you how to structure that:
VBA Code:
Sub MyButtonCode()

    Dim str As String
    Dim mcr As String
    
'   Build string of options to choose from
    str = "Which code would you like to run?" & vbCrLf & _
        "1 - Macro1" & vbCrLf & _
        "2 - Macro2" & vbCrLf & _
        "3 - Macro3"
    
'   Prompt usr for selection
    mcr = InputBox(str, "Select Macro to Run")
    
'   Select which macro to run based on selection
    Select Case mcr
        Case "1"
            Call Macro1
        Case "2"
            Call Macro2
        Case "3"
            Call Macro3
        Case Else
            MsgBox "You have not made a valid selection", vbOKOnly, "PLEASE TRY AGAIN"
    End Select
        
End Sub


Private Sub Macro1()
    MsgBox "Macro1 running"
End Sub


Private Sub Macro2()
    MsgBox "Macro2 running"
End Sub


Private Sub Macro3()
    MsgBox "Macro3 running"
End Sub
So "MyButtonCode" is the VBA code that you would attach to your object/button.
 
Upvote 0
hey @Joe4 good idea which i didnt think of, thanks, it seemed to just hang on the msgbox at the end without updating so i have just removed that part of the code.

thanks
 
Upvote 0
hey @Joe4 good idea which i didnt think of, thanks, it seemed to just hang on the msgbox at the end without updating so i have just removed that part of the code.
Not sure which message box you are referring to, but glad you got it working.
This was just a simple example to show you how to select different macros, which you would need to change for your situation.
 
Upvote 0
If you have the name of the macro in the combobox, then you could use Application.Run and just pass the cell value as the argument - eg

Code:
Application.Run Range("B1").Value

which will run whatever macro name is in B1.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top