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?


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...o&userid=33140
View this thread: http://www.excelforum.com/showthread...hreadid=537239

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"
wrote in
message
...

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?


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile:
http://www.excelforum.com/member.php...o&userid=33140
View this thread:
http://www.excelforum.com/showthread...hreadid=537239

Chip Pearson Wrote:
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

--
Teodomir

-----------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...fo&userid=3314
View this thread: http://www.excelforum.com/showthread.php?threadid=53723

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"
wrote in
message
...

Chip Pearson Wrote:
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 check
boxes on my form, since I have to reset all of them too. And
in this
case, it will work for the command buttons, since all I'm doing
is
setting their backcolor to the default.

But purely for my edification, what if I wanted to do something
like
this to a group of command buttons, not necessarily all of
them? What
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
somehow
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
been
selected, and using that in other computations on the
spreadsheet. But
I had to copy this code for each button, and manually change
the numbers
to indicate which row in the range to affect, and which command
button
to format. This just seems really cumbersome to me, and I'm
hopeful a
more streamlined approach exists that would enable me to write
code
that could be copied without modifications into the click event
for
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 take
the opportunity to learn what I can from it.

Thanks again!


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile:
http://www.excelforum.com/member.php...o&userid=33140
View this thread:
http://www.excelforum.com/showthread...hreadid=537239

Chip Pearson Wrote:
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

--
Teodomir

-----------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...fo&userid=3314
View this thread: http://www.excelforum.com/showthread.php?threadid=53723