Referencing Controls Using Variables

[Teodomiro]

I just finished a project, and everything works fine, but there is some
pretty clunky-looking code making it work. Here's part of my Form
Reset code, which illustrates what I'm talking about:

-----
UserForm1.CommandButton1.BackColor = vbButtonFace
UserForm1.CommandButton2.BackColor = vbButtonFace
UserForm1.CommandButton3.BackColor = vbButtonFace
UserForm1.CommandButton4.BackColor = vbButtonFace
UserForm1.CommandButton5.BackColor = vbButtonFace
UserForm1.CommandButton6.BackColor = vbButtonFace
UserForm1.CommandButton7.BackColor = vbButtonFace
UserForm1.CommandButton8.BackColor = vbButtonFace
UserForm1.CommandButton9.BackColor = vbButtonFace
UserForm1.CommandButton10.BackColor = vbButtonFace
UserForm1.CommandButton11.BackColor = vbButtonFace
UserForm1.CommandButton12.BackColor = vbButtonFace
UserForm1.CommandButton13.BackColor = vbButtonFace
UserForm1.CommandButton14.BackColor = vbButtonFace
UserForm1.CommandButton15.BackColor = vbButtonFace
UserForm1.CommandButton16.BackColor = vbButtonFace
UserForm1.CommandButton17.BackColor = vbButtonFace
UserForm1.CommandButton18.BackColor = vbButtonFace
UserForm1.CommandButton19.BackColor = vbButtonFace
UserForm1.CommandButton20.BackColor = vbButtonFace
-----

I had to do this sort of thing with several different controls. It
seems to me that there ought to be a more elegant way of doing this
sort of thing. Is it possible to reference controls with a variable?
Is there a better way of doing this?

 

[Chip Pearson]

If you want to change all the CommandButtons, use code like the
following:

Dim Ctrl As MSForms.Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CommandButton Then
Ctrl.BackColor = vbButtonFace
End If
Next Ctrl


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Teodomiro"
message
news:[email protected]...

 

[Teodomiro]

If you want to change all the CommandButtons, use code like the
following:

Dim Ctrl As MSForms.Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CommandButton Then
Ctrl.BackColor = vbButtonFace
End If
Next Ctrl

Hi Chip,

Thank you for your suggestion. That will work perfectly for the chec
boxes on my form, since I have to reset all of them too. And in thi
case, it will work for the command buttons, since all I'm doing i
setting their backcolor to the default.

But purely for my edification, what if I wanted to do something lik
this to a group of command buttons, not necessarily all of them? Wha
I have in mind is something like the following construct:

For x = 1 to 20
UserForm1.CommandButton(x).BackColor = vbButtonFace
Next

Of course, that code doesn't work. (Yes, I tried it.)

Another reason I'd like to reference controls with a variable someho
is the code in the button's click event:

-----
Private Sub CommandButton1_Click()

If Sheets("Main").Range("b1") = 1 Then
Sheets("Main").Range("b1") = 0
CommandButton1.BackColor = vbButtonFace
Else
Sheets("main").Range("b1") = 1
CommandButton1.BackColor = vbButtonShadow
End If

(call subroutine to update form using current data)

End Sub
-----

I'm using Column B in Main to keep track of whether a value has bee
selected, and using that in other computations on the spreadsheet. Bu
I had to copy this code for each button, and manually change the number
to indicate which row in the range to affect, and which command butto
to format. This just seems really cumbersome to me, and I'm hopeful
more streamlined approach exists that would enable me to write cod
that could be copied without modifications into the click event fo
each button.

I'm not in a particular hurry here, since I do have code that works.
While this project is still fresh in my mind, though, I'd like to tak
the opportunity to learn what I can from it.

Thanks again

 

[Chip Pearson]

Teodomiro,

One method would be to put the appropriate control names in to an
array, and loop through that array.

Dim CtrlArr As Variant
Dim Ctrl As MSForms.Control
Dim Ndx As Long
' change button names as appropriate
CtrlArr = Array("CommandButton1", "CommandButton2",
"CommandButton3")
For Ndx = LBound(CtrlArr) To UBound(CtrlArr)
Set Ctrl = Me.Controls(CtrlArr(Ndx))
Ctrl.BackColor = vbButtonFace
Next Ndx

Another method might be to put the controls you wish to change
inside a Frame control. Then use code like

Dim Ctrl As MSForms.Control
For Each Ctrl In Me.Frame1.Controls
If TypeOf Ctrl Is MSForms.CommandButton Then
Ctrl.BackColor = vbButtonFace
End If
Next Ctrl


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Teodomiro"
message

 

[Teodomiro]

Another method might be to put the controls you wish to change
inside a Frame control. Then use code like

Dim Ctrl As MSForms.Control
For Each Ctrl In Me.Frame1.Controls
If TypeOf Ctrl Is MSForms.CommandButton Then
Ctrl.BackColor = vbButtonFace
End If
Next Ctrl

Bingo! I completely forgot that frames can be useful.

Thank you, Chip