A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Naming of text box in a macro



 
 
Thread Tools Display Modes
  #1  
Old October 24th 07, 03:16 AM posted to microsoft.public.excel.misc
Shu of AZ
external usenet poster
 
Posts: 130
Default 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.
Ads
  #2  
Old October 24th 07, 01:20 PM posted to microsoft.public.excel.misc
OssieMac
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Naming a range during macro execution Kevryl Excel Discussion (Misc queries) 2 December 11th 06 11:08 AM
Naming worksheets within a macro GLS Excel Worksheet Functions 1 November 14th 06 10:51 AM
Macro for naming sheets? Olle Svensson Excel Discussion (Misc queries) 1 August 3rd 06 03:41 PM
Tab-naming macro only works once :-( Stilla Excel Worksheet Functions 3 February 10th 06 04:09 PM
Naming a Macro with a Letter Carol Excel Discussion (Misc queries) 2 April 19th 05 03:28 AM


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.