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

Paul W 1 Reputation point
2021-10-02T16:16:05.603+00:00

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

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,645 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.5K Reputation points
    2021-10-02T17:40:26.857+00:00

    Try this: 

    Dim ws As Sheet1
Set ws = Sheet1
    0 comments
  2. Paul W 1 Reputation point
    2021-10-04T17:00:27.747+00:00

    Hi @Viorel , 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.