Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Run "Assign Macro" dialog box programmatically

Tags:

excel

vba

Im creating macro tool that allows programmatically create buttons in excel, but at the end i need to invoke "Assign macro" dialog box:

enter image description here

to let user choose what macro he wants assign to created button. How i can achieve that?

Actual code:

Dim btn As Shape
Set btn = wsSheetToAddButton.Shapes.AddFormControl(Type:=xlButtonControl, Left:=buttonLocation.Left, Top:=buttonLocation.Top, Width:=128, Height:=75)
With btn
    With .TextFrame.Characters
        .Caption = buttonText
        With .Font
            .FontStyle = "Bold"
            .ColorIndex = textColor
        End With
    End With
.Select
End With
like image 383
user1916778 Avatar asked Jun 07 '26 17:06

user1916778


2 Answers

Add this after selecting the button:

Application.Dialogs(xlDialogAssignToObject).Show
like image 53
Rory Avatar answered Jun 10 '26 09:06

Rory


If you want to assign the "macro" from the code, this is some minimal example:

Sub TestMe()

    Dim btn As Shape
    Set btn = ActiveSheet.Shapes.AddFormControl(Type:=xlButtonControl, _
                                Left:=5, Top:=5, Width:=75, Height:=75)
    btn.OnAction = "Testing"

End Sub

or refering the newly created button, as mentioned by @Rory:

Sub TestMe()

    Dim btn As Shape        
    Set btn = ActiveSheet.Shapes.AddFormControl(Type:=xlButtonControl, _
                                Left:=5, Top:=5, Width:=75, Height:=75)
    btn.Select: Application.Dialogs(xlDialogAssignToObject).Show

End Sub
like image 24
Vityata Avatar answered Jun 10 '26 09:06

Vityata