Check that the options in User Form selected

[Rainmanne]

I've got a user form to run a code depending on what kind of data I need to upload:

Private Sub UserForm_Initialize()
'Reset the form
radiotempl1.Value = False
radiotempl2.Value = False
datatype.Clear
With datatype
    .AddItem "Public"
    .AddItem "Private"
End With
radiotier1.Value = False
radiotier2.Value = False
radiotier1.Enabled = False
radiotier2.Enabled = False
wipe_format.Value = True
End Sub

Basically, it has two radio buttons, IFT and MMAS, a ComboBox with two options, Public and Private. If an option Private is selected then another combination of radio buttons is activated: tier 1 and tier 2. And there is a checkbox Wipe out Formatting.

I need to make sure that a user first select one of the first radio buttons and an option from the combobox. If they select Private, they have to select tier 1 or tier 2 radio button. The last checkbox should be always selected. I use it for debugging the model. If a required option is not selected, then there should be a message like "Please select a template", and "Please select a datatype" and "Please select a tier". I have found the way to check all the controls but it also checks the inactivated ones. Also I can check individual controls with something like this:

Private Sub modelrun_btn_Click()

Dim x As Long
x = 0
    For Each xControl In frame_template.Controls
            If TypeName(xControl) = "OptionButton" Then
                If xControl.Value = True Then x = x + 1
            End If
            
    Next xControl
If x = 0 Then MsgBox "You must select a template"

UploadData

End Sub

But I cannot figure out how to make it to check only the required controls.

 

[dmt32]

Hi
you can create a common function that only enables required controls when combobox selection completed & also informs user if optionbutton selection missing when commnadbutton pressed.

not tested but see if following helps you

Userform Code Page

Private Sub DataType_Change()
    IsComplete Me
End Sub

Private Sub modelrun_btn_Click()
    
    If Not IsComplete(Me) Then MsgBox "You must Select a template", 48, "Selection Required": Exit Sub
    
    UploadData
    
End Sub

Private Sub UserForm_Initialize()
    
    DataType.List = Array("Public", "Private")
    
    IsComplete Me
    
End Sub

following can be placed either in your userform code page or a standard module

Function IsComplete(ByVal Form As Object) As Boolean
    Dim i             As Long, a As Long
    Dim IsSelected    As Boolean
    Dim ctrl          As String
    
    IsSelected = Form.DataType.ListIndex <> -1
    
    For i = 1 To 2
        ctrl = Choose(i, "radiotier", "radiotemp")
        For a = 1 To 2
            With Form.Controls(ctrl & a)
                .Enabled = DataType.Value = Choose(i, "Private", "Public")
                .Value = IIf(Not .Enabled, False, .Value)
                If Not IsComplete Then IsComplete = .Enabled And .Value
            End With
        Next a
    Next i
    
    With Form.wipe_format
        .Enabled = IsSelected: .Value = .Enabled
    End With
    
    Form.modelrun_btn.Enabled = IsSelected
    
End Function

suggestion may need adjustment to meet specific project need but hopefully, gives you something to progress with

Dave

 

[Rainmanne]

Hi
you can create a common function that only enables required controls when combobox selection completed & also informs user if optionbutton selection missing when commnadbutton pressed.

not tested but see if following helps you

Userform Code Page

Private Sub DataType_Change()
    IsComplete Me
End Sub

Private Sub modelrun_btn_Click()
   
    If Not IsComplete(Me) Then MsgBox "You must Select a template", 48, "Selection Required": Exit Sub
   
    UploadData
   
End Sub

Private Sub UserForm_Initialize()
   
    DataType.List = Array("Public", "Private")
   
    IsComplete Me
   
End Sub

following can be placed either in your userform code page or a standard module

Function IsComplete(ByVal Form As Object) As Boolean
    Dim i             As Long, a As Long
    Dim IsSelected    As Boolean
    Dim ctrl          As String
   
    IsSelected = Form.DataType.ListIndex <> -1
   
    For i = 1 To 2
        ctrl = Choose(i, "radiotier", "radiotemp")
        For a = 1 To 2
            With Form.Controls(ctrl & a)
                .Enabled = DataType.Value = Choose(i, "Private", "Public")
                .Value = IIf(Not .Enabled, False, .Value)
                If Not IsComplete Then IsComplete = .Enabled And .Value
            End With
        Next a
    Next i
   
    With Form.wipe_format
        .Enabled = IsSelected: .Value = .Enabled
    End With
   
    Form.modelrun_btn.Enabled = IsSelected
   
End Function

suggestion may need adjustment to meet specific project need but hopefully, gives you something to progress with

Dave

Hi Dave, thanks a lot for that. I am trying to make it work but this line gives me an error "Object required":

[QUOTE]
.Enabled = DataType.Value = Choose(i, "Private", "Public")
[/QUOTE]

Also can I clarify if I need to replace the code in Private Sub UserForm_Initialize() intirely with your suggestions? Oh, another think. Form in the Function code is not the actual nake of teh user form (which is UserForm1) or is it?

 

[dmt32]

Hi
sorry, thats what happens when type code without testing

try this update

Function IsComplete(ByVal Form As Object) As Boolean
    Dim i             As Long, a As Long
    Dim IsSelected    As Boolean
    Dim ctrl          As String
  
    IsSelected = Form.DataType.ListIndex <> -1
  
    For i = 1 To 2
        ctrl = Choose(i, "radiotier", "radiotemp")
        For a = 1 To 2
            With Form.Controls(ctrl & a)
                .Enabled = Form.DataType.Value = Choose(i, "Private", "Public")
                .Value = IIf(Not .Enabled, False, .Value)
                If Not IsComplete Then IsComplete = .Enabled And .Value
            End With
        Next a
    Next i
  
    With Form.wipe_format
        .Enabled = IsSelected: .Value = .Enabled
    End With
  
    Form.modelrun_btn.Enabled = IsSelected
  
End Function

Also can I clarify if I need to replace the code in Private Sub UserForm_Initialize() intirely with your suggestions?

Yes

Form parameter in Function takes the UserForm object passed to it (Me keyword)

Dave

 

[Rainmanne]

Hi
sorry, thats what happens when type code without testing

try this update

Function IsComplete(ByVal Form As Object) As Boolean
    Dim i             As Long, a As Long
    Dim IsSelected    As Boolean
    Dim ctrl          As String
 
    IsSelected = Form.DataType.ListIndex <> -1
 
    For i = 1 To 2
        ctrl = Choose(i, "radiotier", "radiotemp")
        For a = 1 To 2
            With Form.Controls(ctrl & a)
                .Enabled = Form.DataType.Value = Choose(i, "Private", "Public")
                .Value = IIf(Not .Enabled, False, .Value)
                If Not IsComplete Then IsComplete = .Enabled And .Value
            End With
        Next a
    Next i
 
    With Form.wipe_format
        .Enabled = IsSelected: .Value = .Enabled
    End With
 
    Form.modelrun_btn.Enabled = IsSelected
 
End Function

Yes

Form parameter in Function takes the UserForm object passed to it (Me keyword)

Dave

Sorry, now this line gives "Could not find the specified object" error:

With Form.Controls(ctrl & a)

 

[dmt32]

I got naming of your optionbuttons wrong

change this line

ctrl = Choose(i, "radiotier", "radiotemp")

to this

ctrl = Choose(i, "radiotier", "radiotempl")

Dave

 

[Rainmanne]

I got naming of your optionbuttons wrong

change this line

ctrl = Choose(i, "radiotier", "radiotemp")

to this

ctrl = Choose(i, "radiotier", "radiotempl")

Dave

I should've spotted it myself as I was going through the names a few times.

Now it works but slightly in a wrong way. Basically, I need first select a template and then the data type. Only if the datatype is private I need select a tier. At the moment only the user form appears, the template is greyed out. If I select Private, the option for tier gets activated but template remains greyed out. If I select Public, then the selection for template gets activated but tier remains greyed out. Basically the algorithm is as follows:

1. Select Template: Template1 and Template2
2. Regardless what template is selected, select the DataType: Private and Public
3. If Private: Run the Model, if Public: select a tier: Tier1 and Tier2
4. Run the Model.

 

[dmt32]

As stated, concept was just a suggestion & may need adjustment

If can place copy of your workbook with dummy data in a file sharing site like dropbox & place a link to it I will have a further look for you.

Dave

 

[Rainmanne]

As stated, concept was just a suggestion & may need adjustment

If can place copy of your workbook with dummy data in a file sharing site like dropbox & place a link to it I will have a further look for you.

Dave

Let me try to adjust the code and see how it goes.

 

[dmt32]

Let me try to adjust the code and see how it goes.

that's the best way but if still stuck & can share copy of your workbook - will have a look

Dave