Excel VBA - Wildcards Allowed?

[BGumm]

I have a form with (literally) HUNDREDS of entry fields. I've programmed the form with a standard naming convention, so all input fields are of the form:

txt_R_Description_## (for text boxes)
chk_R_Description_## (for check boxes)

Description is of varying length, as is ## (though ## is limited to either 1 or 2 characters)

The form is tabular with five columns. Essentially, I would like to disable and shade all cells for a given column on certain events happening, such as the user indicating in another cell that s/he has no data for that particular column. Rather than going through them one by one with the code:

txt_R_Desc1_01.Enabled = False
txt_R_Desc1_01.BackColor = &H8000000F
txt_R_Desc2_01.Enabled = False
txt_R_Desc2_01.BackColor = &H8000000F

. . . which is incredibly tedious, does Excel VBA support wildcards and, if so, how do I use them? I know replacing "Desc1" with "*" won't work.

Thanks in advance!

 

[Bong]

Actually, I don't know if you can do it with wildcards, but you can certainly do it with a collection. Say, for instance that you want to deal with the textboxes. You can use the shapes collection:

for each s in sheet1.shapes {
  if left(s.name,5)="txt_R" then
    s.enables=false
    s.backcolor=&H8000000F
  end if
}

or whatever.

_________________
Bob Rashkin

 

[Andrzejek]

.... or if you can assign a text to a Tag property of a control on your Form, you can go thru Controls collection and detect the Tag:

Dim cntlControl As Control

For Each cntlControl In Controls
    If cntlControl.Tag = "ABC" Then
        cntlControl.Enabled = False
        cntlControl.BackColor = vbButtonFace
    End If
Next cntlControl

So all controls with the Tag "ABC" will be dis-abled and gray.


Have fun.

---- Andy

 

[PHV]

Something like this ?

For i = 1 To 20
  Me.Controls("txt_R_Desc" & i & "_01").Enabled = False
  Me.Controls("txt_R_Desc" & i & "_01").BackColor = &H8000000F
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HughLerwill]

Perhaps you will like the Like Operator; check it out in VBA help.

If Control.Name like "*Control" then

 

[combo]

If description does not contain '_', you can use Split function in 'For Each' loop and test conditions, extending Bob's code:
v=split(s.name,"_")
v(0) - 'txt' or chk,
v(2) - description text,
v(3) - ## number (as text).

combo