An array for textboxes in VBA

Question

I have a several textboxes with various names(txtEng, txtCheck, etc) and if they are null then I need their respective textboxes (txtEngMisc, txtCheckMisc, etc) in the same form to be hidden. I already have this on all the AfterUpdates of the textboxes(txtEng, txtCheck, etc):

If Not IsNull(txtDQA) Then
    Me.txtDQAMisc.Visible = True
Else
    Me.txtDQAMisc.Visible = False

End If

I am thinking something like this needs to be on the Onload of the form as well because right now the hidden textboxes are just not showing even when their respective textboxes are NOT Null when the form loads.

I was also thinking this would simplier in an array and maybe even NOT have it on all the AfterUpdates of each textbox.

Ideas, thoughts? Thanks!

Albert D. Kallal · Accepted Answer · 2015-11-24 23:52:46Z

1

Two great ways to handle this.

If you have 10 ccontrols, then adopt a naming convention.

Eg: Ctrl1, Ctrl2 etc.

Then in code to set the 10 controls visible, you can do this:

For i = 1 to 10
  Me("Ctrl" & i).visible = False
Next i

Another great way is to simply set the “tag” property for each control you want to “group” together.

Then you can use this code:

Dim ctrl    As Control

For Each ctrl In Me.Controls
   If ctrl.Tag = "G1" Then
      ctrl.Visible = False
   End If
Next

In above, the tag group is "G1", so you could even build a routine in which you pass the tag code to show or hide a group of controls. The "tag" property can be found on the "other" tab of the property sheet for each control.

If you need to test the value of the control in question, then this will work:

Dim ctrl    As Control

For Each ctrl In Me.Controls
   If ctrl.Tag = "G1" Then
      if isNull(ctrl.Value) = False then
         ctrl.Visible = False
      End If
   End If
Next

edited Nov 24, 2015 at 23:52

answered Nov 23, 2015 at 20:33

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

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

3 Comments

T.Holeman Over a year ago

I like the use of the tag and I see where this going but I'm stuck, yet, again. I now have: Dim ctrl As Control For Each ctrl In Me.Controls If ctrl.Tag = "txtbox" And Not IsNull Then 'respective textboxes are visible (how do I say this and can I use the Tag again)? End If Next

Albert D. Kallal Over a year ago

See the above edited and additional code example. You cannot just use Isnull out of the blue - you have to specify what control or object you are testing for null

T.Holeman Over a year ago

Thank you!! This was a BIG HELP!

Collectives™ on Stack Overflow

An array for textboxes in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related