ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Naming of text box in a macro (https://www.excelbanter.com/excel-discussion-misc-queries/163272-naming-text-box-macro.html)

Shu of AZ

Naming of text box in a macro
 
Is there a way to rename a text box as to identify it in a macro where a
worksheet has many text boxes in it. The macro id's them as Text Box 1, text
box 2 ect. but I would like to edit the line in the macro to delete a text
box I define.

OssieMac

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



All times are GMT +1. The time now is 12:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com