In VBA I can create worksheets and command-buttons but I
want to give them specific 'object names' to make
subsequent programming easier. I realise I cannot do this
programmatically at run time so am looing for a way to do
it on creation, any help would be great,

Ta
Andy

"Andy" wrote in message
...
In VBA I can create worksheets and command-buttons but I
want to give them specific 'object names' to make
subsequent programming easier. I realise I cannot do this
programmatically at run time so am looing for a way to do
it on creation, any help would be great,

Ta
Andy

Andy,
Is this something like what you had in mind?

Option Explicit

Sub Main()
Dim wks As Worksheet
Set wks = Worksheets.Add

Dim cmd As CommandButton
Set cmd = UserForm1.CommandButton1

Call MsgBox(wks.Name & vbNewLine & cmd.Caption)

End Sub

--
Bob Kilmer

This worked ok for me:

Option Explicit
Sub testme01()

Dim OLEObj As OLEObject

Set OLEObj = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=192.75, Top:=57.75, Width:=201.75, _
Height:=54)

With OLEObj
.Name = "CMDBTN_" & .TopLeftCell.Address(0, 0)
End With

'-------

Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons.Add(174.75, 172.5, 157.5, 39)
With myBTN
.Name = "BTN_" & .TopLeftCell.Address(0, 0)
End With

End Sub

Top half for commandbuttons from the control toolbox toolbar and the bottom half
for buttons from the Forms toolbar.


Andy wrote:

In VBA I can create worksheets and command-buttons but I
want to give them specific 'object names' to make
subsequent programming easier. I realise I cannot do this
programmatically at run time so am looing for a way to do
it on creation, any help would be great,

Ta
Andy

--

Dave Peterson

And you really don't even need the object variable:

With ActiveSheet.Buttons.Add(174.75, 172.5, 157.5, 39)
.Name = "BTN_" & .TopLeftCell.Address(0, 0)
End With

or

ActiveSheet.Buttons.Add(174.75, 172.5, 157.5, 39).Name = "BTN_1"

though using the variables may make subsequent references easier.

In article ,
Dave Peterson wrote:

This worked ok for me:

Option Explicit
Sub testme01()

Dim OLEObj As OLEObject

Set OLEObj = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=192.75, Top:=57.75, Width:=201.75, _
Height:=54)

With OLEObj
.Name = "CMDBTN_" & .TopLeftCell.Address(0, 0)
End With

'-------

Dim myBTN As Button

Set myBTN = ActiveSheet.Buttons.Add(174.75, 172.5, 157.5, 39)
With myBTN
.Name = "BTN_" & .TopLeftCell.Address(0, 0)
End With

End Sub