Dim cb As Shape
Set cb = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 625, 55, 60, 50)
cb.OnAction = "UnprotectWorksheets"
ActiveSheet.Buttons("Button 13").Caption = "Change DMR"
I am using this code to add and name a command button to my worksheet, which works the first time used. Unfortunately, the command button number changes each time it is run, so in this line (ActiveSheet.Buttons("Button 13").Caption = "Change DMR") the Button name is different ant the caption "Change DMR" does not get applied. I have tried a few different options, but can't get it to work. Any suggestions??
Try this
Sub ADD_BUTTON_UnprotectWorksheets()
Dim ws As Excel.Worksheet
Dim btn As BUTTON
With ActiveWorkbook.ActiveSheet
Set btn = ActiveSheet.Buttons.Add(625, 55, 60, 50)
btn.OnAction = "UnprotectWorksheets"
btn.Caption = "Change DMR"
btn.Font.Size = 11
btn.Font.Name = "CALIBRI"
End With
End Sub
Thank you, this works great.
You are Welcome 🙂
