Naming objects on creation

[Andy]

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]

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.

 

[J.E. McGimpsey]

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.