Code to make sure checkbox was selected

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB 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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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



Rich (BB code):
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

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows
try this as replacement for the first 7 lines
VBA Code:
    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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.



Rich (BB code):
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
 

Attachments

  • 1720.jpg
    1720.jpg
    88.1 KB · Views: 3

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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...
1615371871674.png


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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
I can change it to look like your advice but with the existing code in place can you assist with me to continue
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

sure.... this might be even better
1615372222932.png
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
It add a value to a worksheet in cell I5.
See this also if it helps ?/

Rich (BB code):
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Attachments

  • 1721.jpg
    1721.jpg
    236.2 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,446
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top