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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Last night i went through the process of each possible option in regards of the code all working correct & can advise it is.

We now need to just concentrate on the RangerPcbNumber userform to make sure the original number 41601 or 41835 also has another selected item BEFORE it can be sent to worksheet.

Thanks
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows
well thats great news. we are heading in the right direction :)
I note the sort button fails also
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Did you have time to look at the code ?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows
this will help us
1615462739771.png
 

diddi

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

ADVERTISEMENT

yes. so describe what happens when you do things
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows
so i looked at RangerFormKey and this is my cleaned up version

VBA Code:
Private Sub TransferButton_Click()
    Dim i As Long, Cancel As Boolean
    Dim x As Long
    Dim ctrl As Control
    Dim lastrow As Long

    Cancel = 0
    If TextBox1.Text = "" Then
        Cancel = True
        MsgBox "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

    ElseIf TextBox2.Text = "" Then
        Cancel = True
        MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox2.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = True
        MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus
        
    ElseIf ComboBox2.Text = "" Then
        Cancel = True
        MsgBox "MAKE A SELECTION NOT SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox2.SetFocus
        
    ElseIf ComboBox3.Text = "" Then
        Cancel = True
        MsgBox "FORD PART NUMBER NOT SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox3.SetFocus

    End If

    If Cancel = True Then
        Exit Sub
    End If
    
    With Sheets("RANGER")
        .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
        .Range("B5").Select

        .Range("B5").Value = TextBox1.Text
        .Range("D5").Value = TextBox2.Text
        .Range("E5").Value = "8C KEY"
        .Range("G5").Value = "8C KEY"
        .Range("C5").Value = ComboBox1.Text
        .Range("H5").Value = ComboBox2.Text
        .Range("F5").Value = ComboBox3.Text
    
        With .Range("I5")
            .Value = "N/A"
            .Font.Size = 14
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
        End With

        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
        .Range("B5").Select
    End With
    
    'Unload RangerFormRemote
    Unload Me
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
End Sub

Private Sub CloseForm_Click()
    Unload RangerFormKey
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
    TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox4_Change()
    TextBox4 = UCase(TextBox4)
End Sub
Private Sub ComboBox1_Change()
    ComboBox1 = UCase(ComboBox1)
End Sub
Private Sub ComboBox2_Change()
    ComboBox2 = UCase(ComboBox2)
End Sub
    Private Sub ComboBox3_Change()
    ComboBox3 = UCase(ComboBox3)
End Sub
Private Sub UserForm_Initialize()
    With ComboBox1
        For Row = 1999 To 2012
            .AddItem Row
        Next Row
    End With

    ComboBox2.AddItem "USED 8C CHIP"
    ComboBox2.AddItem "8C REMOTE"
    ComboBox2.AddItem "VVDI SUPER CHIP"
    ComboBox2.AddItem "HANDY BABY TK5561A"
    
    ComboBox3.AddItem "4044061"
End Sub
 

diddi

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

ADVERTISEMENT

and i cleaned up RangerFormRemote, but i need your description of function as #25. so it is likely not right, and i suspect there are some things that could make it a bit easier to use

VBA Code:
Private Sub TransferButton_Click()
    Dim i As Long, Cancel As Boolean
    Dim x As Long
    Dim ctrl As Control
    Dim lastrow As Long

    Cancel = 0
    If TextBox1.Text = "" Then
        Cancel = True
        MsgBox "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

    ElseIf TextBox2.Text = "" Then
        Cancel = True
        MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox2.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = True
        MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus
        
    ElseIf ComboBox2.Text = "" Then
        Cancel = True
        MsgBox "MAKE A SELECTION NOT SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox2.SetFocus
        
    ElseIf ComboBox3.Text = "" Then
        Cancel = True
        MsgBox "FORD PART NUMBER NOT SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox3.SetFocus

    End If

    If Cancel = True Then
        Exit Sub
    End If
    
    With Sheets("RANGER")
        .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
        .Range("B5").Select

        .Range("B5").Value = TextBox1.Text
        .Range("D5").Value = TextBox2.Text
        .Range("E5").Value = "8C KEY"
        .Range("G5").Value = "8C KEY"
        .Range("C5").Value = ComboBox1.Text
        .Range("H5").Value = ComboBox2.Text
        .Range("F5").Value = ComboBox3.Text
    
        With .Range("I5")
            .Value = "N/A"
            .Font.Size = 14
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
        End With

        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
        .Range("B5").Select
    End With
    
    'Unload RangerFormRemote
    Unload Me
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
End Sub

Private Sub CloseForm_Click()
    Unload RangerFormKey
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
    TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox4_Change()
    TextBox4 = UCase(TextBox4)
End Sub
Private Sub ComboBox1_Change()
    ComboBox1 = UCase(ComboBox1)
End Sub
Private Sub ComboBox2_Change()
    ComboBox2 = UCase(ComboBox2)
End Sub
    Private Sub ComboBox3_Change()
    ComboBox3 = UCase(ComboBox3)
End Sub
Private Sub UserForm_Initialize()
    With ComboBox1
        For Row = 1999 To 2012
            .AddItem Row
        Next Row
    End With

    ComboBox2.AddItem "USED 8C CHIP"
    ComboBox2.AddItem "8C REMOTE"
    ComboBox2.AddItem "VVDI SUPER CHIP"
    ComboBox2.AddItem "HANDY BABY TK5561A"
    
    ComboBox3.AddItem "4044061"
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
On this userform complete CUSTOMERS NAME, VIN, YEAR & also TYPE
In the TYPE selection if you select FERRARI or HYUNDAI FOB then the options below when pressing TRANSFER all the values will be sent to the worksheet.
Because FERRARI / HYUNDAI FOB was selected then N/A is entered into cell L5

This works fine & all checked last night.

If you had selected ORIGINAL 2B then when you press TRANSFER the next userform will open "RangerPcbNumber"
At this point the cell I5 is empty.

So with the new userform open you will see two items 41601 & 41835
Lets say we select 41601
You will now see NALDEC 41601-501-41
Now when you press the TRANSFER button in cell I5 this will be entered N 41601-501-41

If you had selected 41835
Then the options are NALDEC 41803-501-42 & VISTEON 41803-501-42
You then will see entered in cell I5 N 41803-501-42 or V 41803-501-43 depending on which you selected.

So the cell I5 will either have N/A entered or N 41601-50141 / N 41803-501-42 or V 41803-501-43

Currently if you select say 41601 BUT NOT the next option you can press TRANSFER and cell I5 is empty
This goal is to make sure 41601 + the next option or 41835 & either of the next two options BEFORE the values are sent to the worksheet.


I have this code in use below which all works fine APART FROM MAKING SURE THE USER SELECTS OPTIONS AS MENTIONED IN BLUE TEXT ABOVE

The below code needs extra code added so the user MUST select two options

Rich (BB code):
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 OptionButton1.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41601-501-41": OptionButton1.Value = False
    If OptionButton2.Value = True Then .Cells(lastrow + 5, 9).Value = "N 41803-501-42": OptionButton2.Value = False
    If OptionButton3.Value = True Then .Cells(lastrow + 5, 9).Value = "V 41803-501-43": OptionButton3.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 RangerPcbNumber2
    End With
    MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
End Sub
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,299
Office Version
  1. 2010
Platform
  1. Windows
and this is the code for pcbNumber2

VBA Code:
Private Sub ResetButton_Click()
    For Row = 1 To 5
        Controls("OptionButton" & Row).Value = False
    Next Row
End Sub
Private Sub TransferButton_Click()
    If OptionButton1.Value = False And OptionButton2.Value = False And OptionButton3.Value = False Then
        MsgBox "YOU MUST SELECT A CHECKBOX", vbCritical, "NO CHECKBOX WAS SELECTED MESSAGE"
        Exit Sub
    End If
    
     With ThisWorkbook.Worksheets("RANGER")
        If OptionButton1.Value = True Then
            .Cells(lastrow + 5, 9).Value = "N 41601-501-41"
        ElseIf OptionButton2.Value = True Then
            .Cells(lastrow + 5, 9).Value = "N 41803-501-42"
        ElseIf OptionButton2.Value = True Then
            .Cells(lastrow + 5, 9).Value = "V 41803-501-43"
        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

Private Sub OptionButton4_Click()
    Frame1.Visible = True
    Frame2.Visible = False
    OptionButton1.Value = True
End Sub

Private Sub OptionButton5_Click()
    Frame2.Visible = True
    Frame1.Visible = False
End Sub

Private Sub UserForm_Initialize()
    ResetButton.SetFocus
End Sub
 

diddi

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


this works i believe with that code above

set the 2 frames not visible
1615464477908.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,415
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