1

Firstly, I am as beginner as beginner gets. I am trying to take the value from a text box embedded in a user form and then using it in another line of code to activate another text box on a worksheet.

Private Sub CommandButton1_Click()

UserForm2.Hide

Dim FieldName As String
FieldName = UserForm2.TextBox1.Value

Worksheets("Quote").FieldName.Activate
End Sub

I hope you can see what I'm attempting from this code. The error is "Run-time error '438': Object doesn't support this property or method". The value obtained from the textbox is identical to the name of the other one and has been tested to see if it can be retrieved or not.

If someone could tell me the correct functions to use, it'd be much obliged.

Community
  • 1
  • 1
Kris
  • 31
  • 7

1 Answers1

1

Concerning the name of your Sub - Private Sub CommandButton1_Click, I assume that the button is in a form and the TextBox is in a worksheet. In general, there are two types of TextBoxes in Excel - ActiveX control or a Form control and they are handled differently:

What is the difference between "Form Controls" and "ActiveX Control" in Excel 2010?

If you are not sure whether the TextBox is an ActiveX control or a ControlElement, then the following would work. Somehow.

Private Sub CommandButton1_Click()

    On Error Resume Next

    Dim NameOfTextBox As String

    NameOfTextBox = UserForm2.TextBox2.Value
    Worksheets(1).OLEObjects(NameOfTextBox).Activate
    Worksheets(1).Shapes(NameOfTextBox).Select

    Unload Me

    On Error GoTo 0

End Sub

Caution: This is a good example of a "quick and dirty" code, consider only using it for learning reasons.

In production, it is really a good idea to use the Forms as they are supposed to be used (see https://codereview.stackexchange.com/questions/154401/handling-dialog-closure-in-a-vba-user-form) and try to avoid On Error Resume Next.

Vityata
  • 42,633
  • 8
  • 55
  • 100