Case Statement with Option buttons or If Statement with Option Buttons

[Tojomv]

Hi,

This is exercise based so the data is entered on paper and then the data is collected and entered into Excel via a userform.

The User is presented with a screen which has OptionButtons for Team Members 1 to 10, and Question Boxes 1 to 10. The person entering the data, selects the Team Member 1 (for example) and enters the scores in each of the textboxes for questions 1 to 10. Then clicks apply before selecting the next Team Member via OptionsButton and repeats the process.

I have this working via If Then Else Statements, the data is stored in the correct cells in excel, but as you can imagine the code would be considerable (in my untrained status - trying hard to learn), example of the code is below

    If OptBTM4.Value = Checked Then

            Range("B16").Value = TbTMQ1.Text
            Range("C16").Value = TbTMQ2.Text
            Range("D16").Value = TbTMQ3.Text
            Range("E16").Value = TbTMQ4.Text
            Range("F16").Value = TbTMQ5.Text
            Range("G16").Value = TbTMQ6.Text
            Range("H16").Value = TbTMQ7.Text
            Range("I16").Value = TbTMQ8.Text
            Range("J16").Value = TbTMQ9.Text
            Range("K16").Value = TbTMQ10.Text
                    
        Else

This would need to be repeated for each of the 10 question boxes and each of the 10 option buttons, plus I tried doing error checking to ensure the values entered in each of the boxes are between 0 and 9.5, the only way that I could get it to work was to create an if statement for each of the ranges. See Below, (ErrorM - the error Message is declared under the variables and its value is set (msgBox) just below those.

   If OptBTM1.Value = Checked Then


            Range("B13").Value = TbTMQ1.Text
            
               If TbTMQ1.Value = ("<0, OR >9") Then
                    MsgBox ("ErrorM")
               End If
                
            Range("C13").Value = TbTMQ2.Text
            
               If TbTMQ2.Value = ("<0, OR >9") Then
                    MsgBox ("ErrorM")
               End If

That is just for 2 of the textboxes for Team Member 1.

I tried using case statements but could not get it to work, wouldn't write the data to excel

Select Case Checked = True
                    Case OptB1		      
            		Range("B8").Value = TbTMQ1.Text
            		Range("B13").Value = TbTMQ1.Text
            		Range("B18").Value = TbTMQ1.Text
            		Range("B23").Value = TbTMQ1.Text
            		Range("B28").Value = TbTMQ1.Text
            		Range("B33").Value = TbTMQ1.Text
            		Range("B38").Value = TbTMQ1.Text
            		Range("B43").Value = TbTMQ1.Text
            		Range("B48").Value = TbTMQ1.Text
            		Range("B53").Value = TbTMQ1.Text
            
   	End Select

Again this would need to have a case statement for all Team Members (option Buttons) and all 10 questions for each.

Any pointers I would be grateful.

Sorry for the long post.

Regards

 

[dmt32]

Hi,
based on information provided, this probably could be managed with a Function

Not fully tested but give following a try

Code for your UserForm Commandbutton

Private Sub CommandButton1_Click()
    Dim r As Long
    Dim Data() As Variant
    
    If Not IsValidEntry(Me, r, Data) Then Exit Sub
    
    ThisWorkbook.Worksheets("Shee1").Cells(r, 2).Resize(, 10).Value = Data
    
End Sub

Change the control & sheet names shown in RED as required


Place following code either in a STANDARD module or your forms code page

Function IsValidEntry(ByVal Form As Object, ByRef Row As Long, ByRef Data() As Variant) As Boolean
    Dim i As Integer
    ReDim Data(1 To 10)
    For i = 1 To 10
        With Form.Controls("TbTMQ" & i)
            IsValidEntry = CBool(Len(.Text) > 0 And Not Val(.Text) < 0 And Not Val(.Text) > 9.5)
            If Not IsValidEntry Then
                MsgBox "Invalid Entry" & Chr(10) & _
                "Value Entered Must Be In The Range Of:" & Chr(10) & Space(25) & "0 to 9.5", 16, "Invalid Entry"
                .SetFocus
                Exit Function
            End If
            Data(i) = Val(.Text)
        End With
        If Form.Controls("OptBTM" & i).Value Then Row = 12 + i
    Next i
End Function

Solution assumes that the naming convention for your controls is as follows:

for Textboxes

TbTMQ1,TbTMQ2 etc

For OptionButtons

OptBTM1, OptBTM2 etc

Function should handle error checking for valid data entry & report any entry error. It will return completed data entry to the correct row on worksheet (i guessed this part) based on option button selection.

Solution may need some adjustment but should give you something to work with.

Hope Helpful

Dave

 

[Tojomv]

Hi Dave,

Thank you so much for this, worked a treat. I just need to read it through thoroughly to understand it. I tried using functions but could not get them to work either, this was because i was missing the parameters of the function (in the brackets)

Thanks again.

Regards
Mark

 

[My Aswer Is This]

Here is a script I wrote which should do what you want and this sets up the form for the next time you run it to do the next Team.
As you go through each team the Option button turns red showing you which teams have already been entered and set the next OptionButton to true.

Private Sub CommandButton2_Click()
Dim ss As Long
Dim ans As Long
ss = 1
ans = 0
    For Each Control In Me.Controls
        
        If TypeName(Control) = "OptionButton" Then
            If Control.Value = True Then ans = ss
            ss = ss + 1
        End If
    Next
If ans = 0 Then MsgBox "You selected no Option Button": Exit Sub
    For i = 1 To 10
    
        If Controls("TbTMQ" & i).Value < 0 Or Controls("TbTMQ" & i).Value > 9.5 Or Controls("TbTMQ" & i).Value = "" Then
            MsgBox "Some value is less then zero or greater then 9.5" & vbNewLine & "I have stopped the script": Exit Sub
        End If
    Next
        
    For i = 1 To 10
        Cells(12 + ans, i + 1).Value = Controls("TbTMQ" & i).Text: Controls("TbTMQ" & i).Value = ""
    Next

For Each Control In Me.Controls
          
          If TypeName(Control) = "OptionButton" Then
            
            If Control.Value = True Then Control.Value = False: Control.BackColor = vbRed
            If Control.BackColor <> vbRed Then Control.Value = True: Exit Sub
        End If
        Next
End Sub
Private Sub UserForm_Initialize()
OptBTM1.Value = True
End Sub

 

[Tojomv]

Thank you,

I like the idea of colouring the optionbutton to indicate that that team member has been processed. I will add that feature.

Regards
Mark

 

[My Aswer Is This]

Thank you,

I like the idea of colouring the optionbutton to indicate that that team member has been processed. I will add that feature.

Regards
Mark

Not sure I understand add that feature.
That feature is already in my script

And my script clears all the textboxes having them ready for next time.
And sets the next option button to true.

 

[dmt32]

Hi Dave,

Thank you so much for this, worked a treat. I just need to read it through thoroughly to understand it. I tried using functions but could not get them to work either, this was because i was missing the parameters of the function (in the brackets)

Thanks again.

Regards
Mark

hi mark,
glad suggestion worked ok for you. Solution is quite simple in that all processing is managed in one for next loop - if and input error, setfocus places cursor in the appropriate textbox and reports to user - otherwise textbox values are added to the Data array which together with required Row are passed back to the calling procedure. This is one of benefits of using a function in that it simplifies your code.


Good luck with project

Dave