View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
OssieMac OssieMac is offline
external usenet poster
 
Posts: 2,510
Default Naming of text box in a macro

Hi Shu,

I can't say that I really understand your question but I'll post some sample
code from my library of routines and maybe it will help.

Copy the code into a blank workbook and keep a printed copy beside you while
you run it and you should be able to observe what it is doing by following
the MsgBoxes.

Feel free to get back to me if you need more information. A sample of your
code and an a description you want it to do will help me to understand your
request.

Sub Test_Text_Boxes()

Dim objTxtBox1 As Object
Dim objTxtBox2 As Object
Dim objShape As Object

'Create a text box
ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextB ox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=144, _
Top:=12.75, _
Width:=147, _
Height:=27.75).Select

'Assign text box to a variable while it is still selected
Set objTxtBox1 = Selection
MsgBox "Name of first text box created = " & objTxtBox1.Name

'Create another text box
ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextB ox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=336, _
Top:=12.75, _
Width:=147, _
Height:=28.5).Select

'Assign text box to a variable while it is still selected
Set objTxtBox2 = Selection
MsgBox "Name of second text box created = " & objTxtBox2.Name

'Rename first text box
objTxtBox1.Name = "MyFirstTextBox"
MsgBox "New name of first text box = " & objTxtBox1.Name


'Rename second text box
objTxtBox2.Name = "MySecondTextBox"
MsgBox "New name of second text box = " & objTxtBox2.Name

For Each objShape In ActiveSheet.Shapes
MsgBox "Names from For Each Loop = " & objShape.Name
Next objShape

'Delete a text box using assigned variable
objTxtBox1.Delete

'Delete text box using actual text box reference
ActiveSheet.Shapes("MySecondTextBox").Delete

End Sub

'Alternative code for text box name if index is known.
'MsgBox "Name of text box from index = " & ActiveSheet.Shapes(1).Name

Regards,

OssieMac