Frame as variable (newbie question)

[Guest]

Hello all,

I'm using Excel 2000. I have a form with 7 frames, each with 10 comboboxes.
I can loop through all the boxes on each frame if I hardcode it.

for each control in userform1.frame1.controls...

I'd like to be able to change frame1 to a variable so I only have to use one
sub as opposed to 7.

dim x as ???
?set x = combobox1.parent?
for each control in userform1.x.controls...

Basically, I've written a sub that, on the combobox change event, will alert
the user if the same value is in more than one combobox in the same frame. I
don't want to prevent duplication, just let the user know with a msgbox. The
sub is written & works fine, I just can't make it work for more than one
frame. My plan is to declare a public variable, and set it's value in the
change event proc.

If my idea isn't possible, how can I find out what frame the combobox that
changed is in?

Thanks in advance,
Rik

 

[Colo]

Hi Rik,

I have a simple idea. Why don't you name for each
controls depending on the name of the frame?
For example, assume you have 2 frames named Frm1 and Frm2
on the userform.
On Frm1, place 2 TextBox controls as Frm1_TextBox1 and
Frm1_TextBox2.
On Frm2, place 2 TextBox controls as Frm2_TextBox1 and
Frm2_TextBox2.

So, you can use one sub as opposed above 2 Frames using
an argument.
Please have a look at the following procedure.

Private Sub CommandButton1_Click()
InputValue 1
End Sub

Private Sub CommandButton2_Click()
InputValue 2
End Sub

Private Sub InputValue(ByVal FrmIndex As Long)
Dim strFrmPrf As String
strFrmPrf = "Frm" & FrmIndex & "_"
Me.Controls(strFrmPrf & "TextBox1").Text = "Hey"
Me.Controls(strFrmPrf & "TextBox2").Text = "What's Up"
End Sub

HTH
Colo

 

[Tom Ogilvy]

Maybe this will give you some ideas:

Private Sub CommandButton1_Click()
For i = 1 To 7
Set frm = Me.Controls("frame" & i)
Debug.Print "Frame" & i & ":"
For Each ctrl In frm.Controls
Debug.Print ctrl.Name
Next
Next
End Sub

 

[Alex J]

Or....

Private Sub UserForm_Click()
Dim ctl As Control
Dim ctl2 As Control
Dim Fr As Frame
For Each ctl In Me.Controls
If TypeName(ctl) = "Frame" Then
Set Fr = ctl
For Each ctl2 In Fr.Controls
MsgBox Fr.Name & " " & ctl2.Name
Next
End If
Next
End Sub

Alex J