Command Button coding
 

In my quest for the ultimate project goal, i am now trying to place a comman
button on a new sheet.
I can get the code below to place it on the sheet, but when i recorded the
code in did not record the Button Title, nor the sheets("Leave
Blank").select step.

Is there a way i can code this into the below code to create the button?

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=264.75, Top:=516.75, Width:=119.25, _
Height:=22.5).Select

Want the button to be Captioned "EXIT"
and
the clicking of it to selecting sheet named "Leave Blank".

Corey....

Command Button coding
 

Hey Corey,

There are a number of posts that have good examples to solve this
problem (search terms: "programmatically add controls to worksheet").
You want to do something like:

'===========================
Dim ws As Worksheet, oleObj As Excel.OLEObject

Set ws = Sheets(1)

Set oleObj = ws.OLEObjects("CommandButton1")
oleObj.Object.Caption = "EXIT"
With ThisWorkbook.VBProject.vbComponents(ws.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", oleObj.Name) +1, _
vbTab & "ThisWorkbook.Sheets(" & Chr(34) & "Leave Blank" &
Chr(34) & ").Select"
End With
'===========================

Regards,
Steve

Corey schrieb:

In my quest for the ultimate project goal, i am now trying to place a comman
button on a new sheet.
I can get the code below to place it on the sheet, but when i recorded the
code in did not record the Button Title, nor the sheets("Leave
Blank").select step.

Is there a way i can code this into the below code to create the button?

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=264.75, Top:=516.75, Width:=119.25, _
Height:=22.5).Select

Want the button to be Captioned "EXIT"
and
the clicking of it to selecting sheet named "Leave Blank".

Corey....