vba CommandButton.1

[FootBallBat]

Here is my code to create a clickable button.


ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=96.7, Top:=341.25, Width:=100, Height:= _
55).Select
ActiveSheet.OLEObjects(1).Object.Caption = "YES"
ActiveSheet.OLEObjects(1).Object.BackColor = &HFF0000
ActiveSheet.OLEObjects(1).Object.ForeColor = 49152
With Selection
.Name = "YES"
.Object.Caption = "YES"
End With


Now when I go to attach a macro to the button this is what I get.

Private Sub CommandButton1_Click()


End Sub

What Im wondering is, is it possible to change the "CommandButton1" to "YesButton?
The problem I'm having is the "YES" button may not be the first button I add so I would like to have a set name for it.
Thank you

 

[Logit]

.
Your description indicates you are using an ActiveX Control (command button).

When you bring up the small window with the various controls to choose from (on the DEVELOPER tab, INSERT), the top half are FORM CONTROLS and the bottom half are ACTIVEX controls. It is possible to use
FORM CONTROLS on a worksheet - even though the controls are referred to as "FORM". Also .. sometimes an ACTIVEX control can cause problems or 'act up' when using. I most times
will use a FORM CONTROL for that reason.

If you are using an ACTIVEX control and you want the macro "name" to coincide with the purpose/intent of the macro .. one way to accomplish this is in the following example :

Option Explicit


Private Sub CommandButton1_Click()
    MyYesMacro '<---- This calls the macro listed below, where the macro code is located.
End Sub


Sub MyYesMacro()
    'your macro code here
End Sub

As you can surmise from the above, using an ACTIVEX command button can involve a few more steps to get your macro to run.


If you use a FORM CONTROL command button, you only need the macro code itself connected to the button as described in a previous post :

Option Explicit


Sub MyYesMacro()
    'your macro code here
End Sub

Now, you also mentioned about changing the button name. You do that (for an ACTIVEX button) by right clicking (as you have done), bring up the PROPERTIES window, then look for the setting entitled CAPTION. That changes the working on the face of the button.

For a FORM CONTROL command button, you right click the button, select EDIT TEXT and change the button caption directly on the button face itself.


Hopefully the above explanations will give you additional insight to accomplish your goal.