Code to make sure checkbox was selected

[ipbr21054]

Evening.

When my userform opens i have two checkboxes.
CheckBox1 & Checkbox2

The user MUST select one of them to see another checkbox etc before pressing the command button which transfers the values to my worksheet.

Currently there is nothing in place to see if Checkbox 1 or CheckBox 2 was selected.

Please can you advise.
Thanks.

Here is the command button code.

Private Sub TransferButton_Click()

 With ThisWorkbook.Worksheets("RANGER")
    If CheckBox3.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41601-501-41": CheckBox3.Value = False
    If CheckBox4.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41803-501-42": CheckBox4.Value = False
    If CheckBox5.Value = True Then .Cells(lastrow + 5, 9).Value = "V 41803-501-43": CheckBox5.Value = False
 
 
    With .Range("I5")
        .Font.Size = 14
        .Font.Name = "Calibri"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
    End With

    With Sheets("RANGER")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 5).End(xlUp).Row
        .Range("A4:I" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
    End With

    Unload RangerPcbNumber
    End With
    MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
End Sub
Private Sub CheckBox1_Cha

 

[ipbr21054]

So i now have this below.
It now does this
I see two checkboxes so i dont select either but just press the command button.
I see a Msgbox to advise the user that YOU MUST SELECT A CHECKBOX.

You select a checkbox & see its checkbox options of which you make a selection.
Now when you press the command button nothing happens & no error message.

At this point when you press the command button it should add values to the worksheet.

Something ive add isnt quite correct

Private Sub TransferButton_Click()
    If CheckBox1.Value = True Then
       Exit Sub
    ElseIf CheckBox2.Value = True Then
       Exit Sub
    Else
    MsgBox "YOU MUST SELECT A CHECKBOX", vbCritical, "NO CHECKBOX WAS SELECTED MESSAGE"
    End If
    
    With ThisWorkbook.Worksheets("RANGER")
    If CheckBox3.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41601-501-41": CheckBox3.Value = False
    If CheckBox4.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41803-501-42": CheckBox4.Value = False
    If CheckBox5.Value = True Then .Cells(lastrow + 5, 9).Value = "V 41803-501-43": CheckBox5.Value = False
 
 
    With .Range("I5")
        .Font.Size = 14
        .Font.Name = "Calibri"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
    End With

    With Sheets("RANGER")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 5).End(xlUp).Row
        .Range("A4:I" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
    End With

    Unload RangerPcbNumber
    End With
    MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
End Sub

 

[diddi]

try this as replacement for the first 7 lines

    If CheckBox1.Value = False And CheckBox2.Value = False Then
        MsgBox "YOU MUST SELECT A CHECKBOX", vbCritical, "NO CHECKBOX WAS SELECTED MESSAGE"
        Exit Sub
    End If

 

[ipbr21054]

Morning,
That worked great.
I was over thinking about it obviously.
Now i was going to take that information & continue with the other checkboxes but failed at the first option.

I have added a photo of the work flow.
Here is the flow process.

Userform opens with the following options.
CHECKBOX1 & CHECKBOX2 Either must be selected ' This is covered by the code you advised.

If CHECKBOX1 is the selected then you will see CHECKBOX3 appear. Now both must be selected before the COMMAND BUTTON sends values to worksheet.This is were i failed see attached code in RED

If however CHECKBOX2 is selected then you will see CHECKBOX4 & CHECKBOX5 appear.At this stage CHECKBOX2 & EITHER CHECKBOX4 or CHECKBOX5 must be selected before COMMAND BUTTON
sends values to worksheet.

The below codes does this.
Selected CHECKBOX1 but leave CHECKBOX3 not selected & press COMMAND BUTTON.
I do see the MsgBox advising i must select a pcb make so i click on OK & select CHECKBOX3 then i press the COMMAND BUTTON.
I am now expecting the userform values be sent to the worksheet but it still advises me i must select a pcb make & i then see the tick from CHECKBOX3 removed.
So im now in this loop getting nowhere.

Private Sub TransferButton_Click()
    If CheckBox1.Value = False And CheckBox2.Value = False Then
        MsgBox "YOU MUST SELECT A CHECKBOX", vbCritical, "NO CHECKBOX WAS SELECTED MESSAGE"
        Exit Sub
    End If
    
 With ThisWorkbook.Worksheets("RANGER")
    If CheckBox3.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41601-501-41": CheckBox3.Value = False
    If CheckBox4.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41803-501-42": CheckBox4.Value = False
    If CheckBox5.Value = True Then .Cells(lastrow + 5, 9).Value = "V 41803-501-43": CheckBox5.Value = False
 
     If CheckBox1.Value = True And CheckBox3.Value = False Then
        MsgBox "YOU MUST SELECT A MAKE OF PCB", vbCritical, "PCB MAKE WAS NOT SELECTED MESSAGE"
        Exit Sub
    End If
    
    With .Range("I5")
        .Font.Size = 14
        .Font.Name = "Calibri"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
    End With

    With Sheets("RANGER")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 5).End(xlUp).Row
        .Range("A4:I" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
    End With

    Unload RangerPcbNumber
    End With
    MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
End Sub

 

[diddi]

your structure is out of order.... and i wonder if you might be able to do a bit of a redesign of your userform. heres my thoughts for what they are worth...

optionbuttons force a single choice, and make both frames not visible and switch between choices.

 

[ipbr21054]

I can change it to look like your advice but with the existing code in place can you assist with me to continue

 

[diddi]

sure.... this might be even better

 

[diddi]

so question: what is the point of checkbox 3 if there are no other choices and you cant continue if you dont check it

 

[ipbr21054]

It add a value to a worksheet in cell I5.
See this also if it helps ?/

Private Sub TransferButton_Click()
With ThisWorkbook.Worksheets("RANGER")
    Dim i As Long
    Dim x As Long
    Dim ctrl As Control
    Dim lastrow As Long

    Cancel = 0
    If TextBox1.Text = "" Then
        Cancel = 1
        MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

    ElseIf TextBox2.Text = "" Then
        Cancel = 1
        MsgBox "YOU DIDNT ENTER THE VIN", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox2.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = 1
        MsgBox "NO YEAR WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus

    ElseIf ComboBox2.Text = "" Then
        Cancel = 1
        MsgBox "REMOTE TYPE WAS NOT SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox2.SetFocus
    End If

    If Cancel = 1 Then
        Exit Sub
    End If
    
    x = 0
    For i = 1 To 4
     If Me.Controls("OptionButton" & i) = True Then
     x = x + 1
     Opt = i
     End If
     
    Next
    If x = 0 Then
        MsgBox "YOU DIDNT SELECT AN OPTION BUTTON", vbCritical, "RANGER OPTION BUTTON EMPTY MESSAGE"
        Exit Sub
    End If

    Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B5:I5").Borders.LineStyle = xlContinuous
    Range("B5:I5").Borders.Weight = xlThin
    Range("B5:I5").Interior.ColorIndex = 6
    Range("C5:I5").HorizontalAlignment = xlCenter
    Sheets("RANGER").Range("B5").Select
    Cancel = 0
    
    If Cancel = 1 Then
        Exit Sub
        
    End If
    With ThisWorkbook.Worksheets("RANGER")
        .Range("B5").Value = TextBox1.Text
        .Range("D5").Value = TextBox2.Text
        .Range("F5").Value = TextBox3.Text
        .Range("G5").Value = TextBox4.Text
        .Range("C5").Value = ComboBox1.Text
        .Range("H5").Value = ComboBox2.Text
        .Range("E5").Value = Me.Controls("OptionButton" & Opt).Caption
    End With
        
    If ComboBox2.Value = "ORIGINAL 2B" Then

 Unload RangerFormRemote
 RangerPcbNumber.Show
    
 Else
    With .Range("I5")
        .Value = "N/A"
        .Font.Size = 14
        .Font.Name = "Calibri"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
        Unload RangerFormRemote
     End With

    With Sheets("RANGER")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 5).End(xlUp).Row
        .Range("A4:I" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
        MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
    End With
        Application.ScreenUpdating = True
    Range("B6").Select
    Range("B5").Select
    End If
 End With
 
End Sub

 

[ipbr21054]

I see you have 2 x Frame 1 i assume mistake ?
Also in right frame 2 x option button1 and not 1 and 2 ?

see my form attached.