Check whether a named textbox exist in the excel using VBA

Question

Platform : MS Excel 2007(MS Visual Basic 6.0)

I have a few excel file with different textboxes in them. All the textboxes were name. E.g TxTbox_AAAA, TxtBox_BBBB Most of them have similar number of Textboxes with same name.

I also required to update the content inside the textboxes. But like i said... some excel file doesn't contain the textboxes.

E.g aaa.xls and bbb.xls have TexTbox_AAAA, TextBox_BBBB and ccc.xls only have TexTbox_AAAA

my script is something like this

xlApp.ActiveSheet.TextBoxes("TextBox_AAAA").Text = TxtAAAA
xlApp.ActiveSheet.TextBoxes("TextBox_BBBB").Text = TxtBBBB

but if i run, it will encounter run-time error '1004': Unable to get the TextBoxes property of the Worksheet class

which i suspect it is due to the excel doesn't have this textbox named "TextBox_BBBB"

so how do i put a check before xlApp.ActiveSheet.TextBoxes("TextBox_BBBB").Text = TxtBBBB

to check if this worksheet/activesheet doesn't contain TextBoxes("TextBox_BBBB"), it will not execute this step?

Gary's Student · Accepted Answer · 2014-02-15 14:17:43Z

3

Since a TextBox is a Shape, here is one way to tell if a specific TextBox is on the activesheet:

Public Function IsItThere(sIn As String) As Boolean
    IsItThere = False
    If ActiveSheet.Shapes.Count = 0 Then Exit Function
    For Each s In ActiveSheet.Shapes
        If s.Name = sIn Then
            IsItThere = True
            Exit Function
        End If
    Next s
End Function

answered Feb 15, 2014 at 14:17

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leon Over a year ago

hmm... i am not very familiar in adding public function into my script... so i can just copy and paste this function and edit the value into my script?

Gary's Student Over a year ago

You could adapt it as in L42 's Post

L42 · Accepted Answer · 2014-02-15 14:28:10Z

1

Not sure but try this:

Dim ws as Worksheet
Dim shp as Shape

Set ws = Activesheet '~~> change to suit

For Each shp in ws.Shapes
    If shp.Name = "Textbox_Name" Then
        '~~> your code here
    End If
Next

Hope this helps

answered Feb 15, 2014 at 14:28

L42

19.8k11 gold badges47 silver badges70 bronze badges

1 Comment

Leon Over a year ago

mine is the same as urs but the return value is as per stated.. "Textbox 1","Textbox 2","Textbox 3", instead of "Textbox_AAAA", etc

Leon · Accepted Answer · 2014-02-15 15:03:36Z

0

Thanks L42 and Gary's Student

I got it.. i should put

For Each s In xlApp.ActiveSheet.Shapes

instead of

For Each s In ActiveSheet.Shapes

only

Thanks for the help!!! =)

answered Feb 15, 2014 at 15:03

Leon

1812 gold badges4 silver badges16 bronze badges

Collectives™ on Stack Overflow

Check whether a named textbox exist in the excel using VBA

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related