question

PaulW-4888 avatar image
0 Votes"
PaulW-4888 asked Viorel-1 commented

Available worksheet methods change when using 'Set as' variable name

I have a single textbox on a worksheet. When using the actual worksheet name ("Sheet1") the "TextBox1" option is available in intellisense and the code works when the macro is executed. When using a variable name (i.e., "Set ws = Sheet1"), the "TextBox1" option is not available in intellisense and the macro generates an error ("Compile error: Method or data member not found"). Why is the textbox option not available when using the variable name? Thanks in advance for your help.

Option Explicit

Sub TestBox()
Dim ws As Worksheet
Dim txt1 As String, txt2 As String

 Set ws = Sheet1
    
 txt1 = Sheet1.TextBox1.Value    'no error
 txt2 = ws.TextBox1.Value        'error
    
 Debug.Print "txt1: " & txt1
 Debug.Print "txt2: " & txt2

End Sub

137096-notextboxoption.jpg


office-excel-itprooffice-vba-dev
notextboxoption.jpg (59.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Try this:

 Dim ws As Sheet1
 Set ws = Sheet1


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PaulW-4888 avatar image
0 Votes"
PaulW-4888 answered Viorel-1 commented

Hi @Viorel-1 , thanks for the input, however your suggestion still gives me an error. For anyone who may have the same problem, identifying the textbox as an OLE item works (solution provided by a microsoft volunteer). Using

txt2 = ws.OLEObjects("TextBox1").Object.Value

gives access to the textbox without error. Appreciate your time nonetheless.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What error did you get and what code did you try?

0 Votes 0 ·