I have a bunch of textboxes from the Excel Drawing toolbar on a worksheet.
When I select a textbox I can see its codename in the Excel Namebox (upper
left corner of worksheet under file menu). Is there a VBA collection object
in Excel that would contain these drawing toolbar textbox objects so I could
loop through this collection? I want to loop through this collection or at
least be able to reference the textboxes through VBA so I can change the text
programmatically. How to do this?

Thanks,
Rich

I usually loop through the shapes collection on the worksheet and look for
shapes that meet a certain need. At first, I'd probably look for shape
names that contain text.

HTH,
Barb Reinhardt

"Rich" wrote:

I have a bunch of textboxes from the Excel Drawing toolbar on a worksheet.
When I select a textbox I can see its codename in the Excel Namebox (upper
left corner of worksheet under file menu). Is there a VBA collection object
in Excel that would contain these drawing toolbar textbox objects so I could
loop through this collection? I want to loop through this collection or at
least be able to reference the textboxes through VBA so I can change the text
programmatically. How to do this?

Thanks,
Rich

Well, so far I found the Shapes collection. Actually, I have a chart and
several textboxes from the drawing toolbar. When I loop through Shapes it
will identify the chart but only one of the drawing toolbar textboxes. I
have about 10 of them. How come the other 9 textboxes are not being
recognized? How can I get VBA to recognize them?


"Rich" wrote:

I have a bunch of textboxes from the Excel Drawing toolbar on a worksheet.
When I select a textbox I can see its codename in the Excel Namebox (upper
left corner of worksheet under file menu). Is there a VBA collection object
in Excel that would contain these drawing toolbar textbox objects so I could
loop through this collection? I want to loop through this collection or at
least be able to reference the textboxes through VBA so I can change the text
programmatically. How to do this?

Thanks,
Rich

If they are textboxes from the Drawing toolbar:

Dim myTB as Textbox
for each myTB in activesheet.textboxes
msgbox "Found one!"
next mytb

If they are textboxes from the Control Toolbox toolbar:

dim OLEObj as OLEObject
for each oleobj in activesheet.oleobjects
if typeof oleobj.object is msforms.textbox then
msgbox "found another one!"
end if
next oleobj




Rich wrote:

I have a bunch of textboxes from the Excel Drawing toolbar on a worksheet.
When I select a textbox I can see its codename in the Excel Namebox (upper
left corner of worksheet under file menu). Is there a VBA collection object
in Excel that would contain these drawing toolbar textbox objects so I could
loop through this collection? I want to loop through this collection or at
least be able to reference the textboxes through VBA so I can change the text
programmatically. How to do this?

Thanks,
Rich

--

Dave Peterson

Thanks all for the replies. I will give them a try.

"Rich" wrote:

I have a bunch of textboxes from the Excel Drawing toolbar on a worksheet.
When I select a textbox I can see its codename in the Excel Namebox (upper
left corner of worksheet under file menu). Is there a VBA collection object
in Excel that would contain these drawing toolbar textbox objects so I could
loop through this collection? I want to loop through this collection or at
least be able to reference the textboxes through VBA so I can change the text
programmatically. How to do this?

Thanks,
Rich