Disable Option Button based on another Option Button, when all buttons are defined in a class

[Jameo]

Hi guys,

I am trying to diable an option button, dependant on a user selection.

I have my modules as follows:

ThisWorkbook:

Private Sub Workbook_Open()
    
    Dim ctrl As MSForms.Control
    Set col = New Collection
    
    ' This loop connects all buttons in UserForm
    ' to one event hanlder in EventsTrapper class.
    For Each ctrl In frmTest.Controls
        If TypeOf ctrl Is MSForms.OptionButton Then
            Set EventTrapper = New EventsTrapper
            Set EventTrapper.Button = ctrl
            Set EventTrapper.Form = frmTest
            col.Add EventTrapper
            Set EventTrapper = Nothing
        End If
    Next
End Sub

Standard:

Public col As Collection
Public EventTrapper As EventsTrapper

and EventsTrapper class module:

Public WithEvents Button As MSForms.OptionButton
Public Form As MSForms.UserForm
Private Sub Button_Change()
  With frmTest
    If .OptSum.Value = True Then
        .optSeaH.Enabled = False
    End If
End With
        
        
End Sub

I cant get the optSeaH to be disbaled when OptSum is true.

Any ideas?

Thanks in advance.

 

[Sektor]

Hi, Jameo!
Your code is incorrect because Private Sub Button_Change() is raised by Button variable in your class! You must get info from Button variable!

 

[Jameo]

Ahhh Sektor, my own personal VBA Guru

How would I go about doing that exactly mate?

Thanks

 

[Sektor]

Here is little explanation of internal logic.

You need following:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Button_Change()
  [COLOR="Blue"]With[/COLOR] frmTest
    [COLOR="Blue"]If[/COLOR] [B][COLOR="Red"]Button[/COLOR][/B].Value = [COLOR="Blue"]True[/COLOR] [COLOR="Blue"]Then[/COLOR]
        .optSeaH.Enabled = [COLOR="Blue"]False[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]

 

[Jameo]

Cheers mate.

Unfortunately my works filter is once again blocking that site - **** thing!
As I have multiple buttons defined in the button variable, can I not have Button.OptSum.Value = true?

This is because I only wish for this action to be completed when a unqiue button is changed, and not everyone of them

 

[RoryA]

You are kind of violating the purpose of the class, which is that it should be self-contained. If each option button is dependent on the same controlling option button, you should assign that controlling option button as an additional control in the class, and then simply test its value in the change event.
Also, the code that sets up the collection and class really ought to be in the form's Initialize event, not in the Workbook_Open event, and your EventTrapper and collection variables belong in the userform too.

 

[Sektor]

Ah, yes! Rory, it's true that code in ThisWorkbook module must be in Form's Initialize event. I forgot to change it because before it I was adding controls from sheet, and the code should be in ThisWorkbook module.

To Jameo: Place all code in ThisWorkbook module to Form's Initialize event handler!

 

[Jameo]

Ok Rorya,

I'll change the variables into a form intialise sub. How would I go about setting the controlling option button as an additional control in the class?

I essentially have two controlling option buttons for summer and winter: OptSum and OptWin.

Based on the selection of this two buttons, some of the dependant option buttons should become disabled.

After this event, I then plan to assign further public variables certain values dependant on the selection of the option buttons in the form.


A little example: (btw this is all about certain flows in piplines just for a bit of background information)

I have optSum and OptWin as the controlling buttons.

I have a number of options for a pipeline (called SEAL) for low, medium and high flows (optSeaL, optSeaM, optSeaH)

If the user chooses winter initially, i then want optSealL to become diabled (as there is never low flows from this pipeline in the winter)

Therefore, the user can then select optSeaM or optSeaH.

If optSeaM was selected, I would then change the valSealF variable to a certain value based on a cell reference.

In the class module I was hoping to do this by having something like:

With frmTest
If Button.OptWin = True Then
.opSeaL.Enabled = False
End If

If .optSeaM = True Then
valSealF = sheets("Control").range("E10")
End If
End With

or something along those lines, with similar set ups for all the pipelines on my form.

After this several calculations would be completed.

Any help you could lend would be greatly appreicated.

Thanks

 

[RoryA]

So your form code becomes:

Dim col As Collection

Private Sub UserForm_Initialize()
   Dim EventTrapper As EventsTrapper
   Dim ctrl As MSForms.Control
   Set col = New Collection

   ' This loop connects all buttons in UserForm
   ' to one event hanlder in EventsTrapper class.
   For Each ctrl In Me.Controls
      If TypeName(ctrl) = "OptionButton" Then
         ' don't need to handle optsum
         If Not ctrl Is optSum Then
            Set EventTrapper = New EventsTrapper
            Set EventTrapper.Button = ctrl
            Set EventTrapper.btnController = optSum
            col.Add EventTrapper
         End If
      End If
   Next
End Sub

and the class is:

Option Explicit

Public WithEvents Button As MSForms.OptionButton
Public WithEvents btnController As MSForms.OptionButton
Private Sub btnController_Change()
   ' disable other option buttons if controller option is True
   Button.Enabled = Not btnController.Value
End Sub

Private Sub Button_Change()
   ' code for other option buttons goes here
End Sub

You could also use a separate class to handle optSum and use that to disable all the other option buttons on the parent form. (note: I'm assuming you have some means of clearing optSum?!)

 

[RoryA]

Just seen your last post - you need two additional controls then - one for optSum and one for OptWint and then change the enabled state of the other controls as appropriate.