VBA Refer to TextBox or Label using a loop

Question

I am trying to replace the following:

txt1.Text = ""
txt2.Text = ""
txt3.Text = ""
txt4.text = ""
...continues for quite awhile

With:

Dim cCont As Control

For Each cCont In Me.Controls

If TypeName(cCont) = "TextBox" Then
'reset textbox value
   ???
End If
Next cCont

How do I refer to the textbox using the generic 'Control'?

GSerg · Accepted Answer · 2011-05-19 14:31:38Z

9

You already have your control in cCont.

Dim cCont As Control

For Each cCont In Me.Controls
  If TypeOf cCont Is MSForms.TextBox Then
    cCont.Text = "hi"
    MsgBox cCont.Name
  End If
Next

If you're confused by the fact you don't get the Text property from IntelliSense, just cast the Control to a more derived type:

Dim cCont As Control

For Each cCont In Me.Controls
  If TypeOf cCont Is MSForms.TextBox Then
    Dim cText As MSForms.TextBox

    Set cText = cCont

    cText.Text = "hi"
    MsgBox cText.Name
  End If
Next

This will use early binding instead of late binding, and you'll get your code suggestions.

edited May 19, 2011 at 14:31

answered May 19, 2011 at 14:26

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

9 Comments

ray Over a year ago

It is good practice to declare all local variables immediately after you sub or function header. "Variables declared within a block inside a procedure (such as a For loop) are initialized on entry to the procedure. These initializations take effect whether or not your code ever executes the block." source link

GSerg Over a year ago

@ray023 Your comment is highly irrelevant. It concerns VB.NET, which is different to VBA, and it does not affect the way cText is used. Nobody cares if cText is automatically set to Nothing when you enter the procedure. The point is, it won't be set to cCont until you get into the loop.

ray Over a year ago

I'm somebody and I care, dang-it!! :D The link may have been VB.NET, but the variable lifetime as mentioned from the link also applies to VBA. The point is cText lives in the procedure, not in the FOR EACH LOOP. So wouldn't you rather just group all the variables together in a nice, tight package? I know it's nitpicking and I still gave +1 FWIW.

GSerg Over a year ago

@ray023 You seem to be confusing lifetime with scope :) Life time = entire procedure, scope = ? In VBA, it's "from the point of declaration to the end of the procedure." In VB.NET, it's "from the point of declaration to the end of enclosing block." The fact that a variable lives at a certain moment doesn't mean you can use it. You can only use it where lifetime and scope overlap. This is why I often find it useful to declare variables where they are used.

GSerg Over a year ago

@Oneide All controls on the form get enumerated, not only textboxes. When a non-textbox is going to be fetched, it fails to assign to a TextBox variable. But all controls can be assigned to a Control variable, which is the base class for them.

|

Collectives™ on Stack Overflow

VBA Refer to TextBox or Label using a loop

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related