Passing frame object in code

[Marc]

Hi,

I have a problem with the code below.
I cant understand why Im not allowed to pass on the frame object for
later use in the code.
I am using this because I have several comboboxes in different frames
that should use the same code.


Sub ComboBox_6_Rental_RQ_DropButtonClick()
Dim ActiveFrame As Control
Dim Ctrl As Control

Set ActiveFrame = Me.Frame_6_Rental

If Me.ActiveControl = "Yes" Then

For Each Ctrl In Me.ActiveFrame.Controls
If TypeName(Ctrl) = "ComboBox" Or TypeName(Ctrl) =
"TextBox" Then
Me.ActiveFrame.Ctrl.Enabled = True
Me.ActiveFrame.Ctrl.BackColor = RGB(255, 255, 255)
End If
Next
Else

For Each Ctrl In Me.ActiveFrame.Controls
If TypeName(Ctrl) = "ComboBox" Or TypeName(Ctrl) = "TextBox"
Then
Me.ActiveFrame.Ctrl.Enabled = True
Me.ActiveFrame.Ctrl.BackColor = RGB(150, 150, 150)
End If
Next

End If

End Sub

Hope you can help me.

Cheers

Marc

 

[JLGWhiz]

I tried ActiveFrame in the Object Browser and got a no results answer. Maybe
you should use the Frame name.

 

[JLGWhiz]

Disregard, I missed the Set statement.

 

[JLGWhiz]

Out of curiosity, did you expect to ever get a result from the Else part of
your If statement when the criteria is identical to the original If criteria?
If the first part is not true, the second part won't be either.

 

[Tim Zych]

The concept is sound, but remove the form reference from the frame & control
variables:

For Each Ctrl In Me.ActiveFrame.Controls
If TypeName(Ctrl) = "ComboBox" Or TypeName(Ctrl) = "TextBox"
Then
Me.ActiveFrame.Ctrl.Enabled = True
Me.ActiveFrame.Ctrl.BackColor = RGB(150, 150, 150)
End If
Next

should be:

For Each Ctrl In ActiveFrame.Controls
If TypeName(Ctrl) = "ComboBox" Or TypeName(Ctrl) = "TextBox" Then
Ctrl.Enabled = True
Ctrl.BackColor = RGB(150, 150, 150)
End If
Next

If Me.ActiveControl = "Yes" Then

Assuming you mean ActiveControl.Caption ? That line didn't work.

 

[Marc]

Hi,

Tim, thanks for your help. I corrected the code as you said plus added
a bit more as I found out the me.activecontrol is actually the frame
not the Combobox
which is pressed.
Therefor i used me.activecontrol.activecontrol.value.

JLG, if you take a close look a the code, then the if statement should
make senese as I want to set diffenrent properties of the combobox if
the value selected from the list is "yes" or not.

Here is the final code which I can use in all my userform with regards
to the controls in the frames.


Sub ComboBox_6_Rental_RQ_dropbuttonclick()
Call ActivateFields(4)
End Sub

Sub ActivateFields(FormNumber As Integer)

Dim MyForm As UserForm
Dim ActiveFrame As Control
Dim Ctrl As Control
Dim ActiveControlName As String

Set MyForm = UserForms(FormNumber)
'First activecontrol has focus on frame
Set ActiveFrame = MyForm.ActiveControl

'Next activecontrol has focus on selected combobox
ActiveControlName = MyForm.ActiveControl.ActiveControl.Name

'If TypeName(Ctrl) <> "ComboBox" Or TypeName(Ctrl) <> "TextBox" Then
'Exit Sub
'End If


If MyForm.ActiveControl.ActiveControl.Value = "Yes" Then

For Each Ctrl In ActiveFrame.Controls
If (TypeName(Ctrl) = "ComboBox" Or TypeName(Ctrl) =
"TextBox") And Ctrl.Name <> ActiveControlName Then
Ctrl.Enabled = True
Ctrl.BackColor = RGB(255, 255, 255)
End If
Next
Else

For Each Ctrl In ActiveFrame.Controls
If (TypeName(Ctrl) = "ComboBox" Or TypeName(Ctrl) = "TextBox")
And Ctrl.Name <> ActiveControlName Then
Ctrl.Enabled = True
Ctrl.BackColor = RGB(150, 150, 150)
End If
Next

End If

End Sub

Thanks again Tim.

Best regards

Marc Bruun