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

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
Add this after selecting the button:
Application.Dialogs(xlDialogAssignToObject).Show
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With