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]

Re: vba CommandButton.1 need help please

.
This works here :

Option Explicit


Sub YesBtn()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=96.7, Top:=[COLOR=#333333]341.25[/COLOR], 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
End Sub

 

[FootBallBat]

Re: vba CommandButton.1 need help please

That does not work, cause when you click on design mode and double click to add a macro it comes up as:
Private Sub CommandButton1_Click()


End Sub

so its still called CommandButton1

 

[Logit]

Re: vba CommandButton.1 need help please

.
Are you placing the CommandButton on a worksheet or on a Userform ?

 

[FootBallBat]

Re: vba CommandButton.1 need help please

.
Are you placing the CommandButton on a worksheet or on a Userform ?

It a module so I'm guessing since it works on every page its a Userform

 

[Logit]

Re: vba CommandButton.1 need help please

.
Here is a worksheet : https://www.amazon.com/clouddrive/share/tWl7pIB6eMkAhYRhoA873tC17swGX0E819G46ZPeGTC

Here is a User Form : https://www.amazon.com/clouddrive/share/XGTw8bGb6Y57tJBgNwKA4loN34GiNWMKtVwy7tuYIeE

Here is a Worksheet with UserForm displayed : https://www.amazon.com/clouddrive/share/vE7seAOyZIOxGOSt2B8Eqiw8sDaWusDcZSDyyiHQ9ph


Where you are placing the Command Button makes a difference how to proceed.

 

[FootBallBat]

Re: vba CommandButton.1 need help please

.
Here is a worksheet : https://www.amazon.com/clouddrive/share/tWl7pIB6eMkAhYRhoA873tC17swGX0E819G46ZPeGTC

Here is a User Form : https://www.amazon.com/clouddrive/share/XGTw8bGb6Y57tJBgNwKA4loN34GiNWMKtVwy7tuYIeE

Here is a Worksheet with UserForm displayed : https://www.amazon.com/clouddrive/share/vE7seAOyZIOxGOSt2B8Eqiw8sDaWusDcZSDyyiHQ9ph


Where you are placing the Command Button makes a difference how to proceed.

Thank you it is placed on worksheet

 

[Logit]

Re: vba CommandButton.1 need help please

.
If it is the first button you have placed on the worksheet, it will automatically be named " Button 1 " .

You can create a macro in a routine module like so :

Option Explicit


Sub btnYes()


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
End Sub

The name of the macro (here it is btnYes) can be anything you want:

Sub ThisIsMyCommandButtonYes()
'macro code
End Sub

Sub HeyButtonYes()
'macro code
End Sub

etc.

etc.

Once you have created your macro, go back to the worksheet, RIGHT CLICK the command button and select ASSIGN MACRO. A small form will appear with a list of available macros. Simply select the macro name yo want
and click OK.

Now the command button is attached to that macro. Clicking the button activates the macro.

 

[Logit]

Re: vba CommandButton.1 need help please

.
It is late here now. Almost midnight. Have to get some sleep.

 

[FootBallBat]

Re: vba CommandButton.1 need help please

Well It still does not work, the only way I know how to change the name is to right click/ properties/ then change (name) on the top.