array of userform controls
 

Hello,

Can I define an array of userform controls? This doesn't work- I get a
"type mismatch":

Dim userform_control As MSForms.Control
With GoalSeekwithValueRangeForm
For Each userform_control In .Controls (Array(.ChangingCellRefEdit.Name,
..SetCellRefEdit.Name))
...

Thanks as always,

Doug

array of userform controls
 

Doug,

Here is code I use to access the option buttons
on a particular form. You can probably adapt it to your needs.

Regards,
Jim Cone
San Francisco, USA

'-------------------------
Private Function SetButtonFont(ByRef objControl As MSForms.OptionButton)
Dim objButt As Object
For Each objButt In Me.ButtonsFrame.Controls
If TypeOf objButt Is MSForms.OptionButton Then objButt.Font.Bold = False
Next 'objButt
objControl.Font.Bold = True
Set objButt = Nothing
Set objControl = Nothing
End Function
'-------------------------



"Doug Glancy" wrote in message
...
Hello,

Can I define an array of userform controls? This doesn't work- I get a
"type mismatch":

Dim userform_control As MSForms.Control
With GoalSeekwithValueRangeForm
For Each userform_control In .Controls (Array(.ChangingCellRefEdit.Name,
..SetCellRefEdit.Name))

Thanks as always,
Doug

array of userform controls
 

Jim,

I'm only working with two controls, so I thought something analogous to

Worksheets(Array("Sheet1", "Sheet2"))

would be more compact than what I'm doing, which is like what you said, only
the If statement specifies the two controls' names:

With GoalSeekwithValueRangeForm
For Each userform_control In .Controls
If userform_control.Name = .SetCellRefEdit.Name Or _
userform_control.Name = .ChangingCellRefEdit.Name Then
...
Thanks,

Doug

"Jim Cone" wrote in message
...
Doug,

Here is code I use to access the option buttons
on a particular form. You can probably adapt it to your needs.

Regards,
Jim Cone
San Francisco, USA

'-------------------------
Private Function SetButtonFont(ByRef objControl As MSForms.OptionButton)
Dim objButt As Object
For Each objButt In Me.ButtonsFrame.Controls
If TypeOf objButt Is MSForms.OptionButton Then objButt.Font.Bold =
False
Next 'objButt
objControl.Font.Bold = True
Set objButt = Nothing
Set objControl = Nothing
End Function
'-------------------------



"Doug Glancy" wrote in message
...
Hello,

Can I define an array of userform controls? This doesn't work- I get a
"type mismatch":

Dim userform_control As MSForms.Control
With GoalSeekwithValueRangeForm
For Each userform_control In .Controls
(Array(.ChangingCellRefEdit.Name,
.SetCellRefEdit.Name))

Thanks as always,
Doug

array of userform controls
 

Hi Doug

Yes, add them to a custom Collection. Try this on a new userform containing
Textbox1, Textbox2, Textbox3, Textbox4, ...

Option Explicit 'top of userform module

Dim MyControls As Collection

Private Sub UserForm_Initialize()
Set MyControls = New Collection
MyControls.Add Me.TextBox1, "Txt1"
MyControls.Add Me.TextBox2, "Txt2"
MyControls.Add Me.TextBox3, "Txt3"
End Sub

Private Sub CommandButton1_Click()
Dim ctl As MSForms.Control 'or .TextBox
For Each ctl In MyControls
ctl.Text = Now
Next
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
Set MyControls = Nothing
End Sub

HTH. Best wishes Harald

"Doug Glancy" skrev i melding
...
Hello,

Can I define an array of userform controls?