vba CommandButton.1

FootBallBat

Board Regular
Joined
Jan 26, 2012
Messages
169
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Re: vba CommandButton.1 need help please

.
This works here :

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
Re: vba CommandButton.1 need help please

.
Are you placing the CommandButton on a worksheet or on a Userform ?
 
Upvote 0
Upvote 0
Re: vba CommandButton.1 need help please



Thank you it is placed on worksheet
 
Upvote 0
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 :

Code:
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.
 
Upvote 0
Re: vba CommandButton.1 need help please

.
It is late here now. Almost midnight. Have to get some sleep.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top