CODEWITHSUNDEEP
vbaexcelexcel-2007
Leon
Leon

Reputation: 181

Check whether a named textbox exist in the excel using VBA

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?

Upvotes: 0

Views: 5844

Answers (3)

Leon
Leon

Reputation: 181

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!!! =)

Upvotes: 0

L42
L42

Reputation: 19737

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

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

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

Upvotes: 3

Related Questions