Naming objects on creation

  • Thread starter Thread starter Andy
  • Start date Start date
A

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
 
Andy said:
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
Click to expand...

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
 
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.
 
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.
 
Back
Top