Show MsgBox if OptionButton not selected

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,909
Office Version
  1. 2007
Platform
  1. Windows
Afternoon.
Below is part of my code to show a MsgBox if a TextBox on the userform has been left blank.

Please advise how i can also add the same but for OptionButtons.
I have 4 OptionButtons & 1 must be selected or show MsgBox like example below.

I did try the same format for each button but after selecting optionButton 1 i was told that 2 was not selected etc etc
So the MsgBox would be shown all the time as it was expecting ALL to be selected as opposed to just the 1 being selected

Rich (BB code):
If TextBox1.Text = "" Then
    Cancel = 1
    MsgBox "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox1.SetFocus
    
ElseIf TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox2.SetFocus
    
ElseIf TextBox3.Text = "" Then
    Cancel = 1
    MsgBox "FORD PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox3.SetFocus
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,909
Office Version
  1. 2007
Platform
  1. Windows
Can you please just put the whole code here as im getting confused now, i can then copy & paste
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Private Sub TransferButton_Click()

    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 "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

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

    ElseIf TextBox3.Text = "" Then
        Cancel = 1
        MsgBox "FORD PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox3.SetFocus

    ElseIf TextBox4.Text = "" Then
        Cancel = 1
        MsgBox "ITEM IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox4.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = 1
        MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus

    ElseIf ComboBox2.Text = "" Then
        Cancel = 1
        MsgBox "TYPE IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox2.SetFocus

       
        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
            End If
        Next
        If x = 0 Then
            MsgBox "MY PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            Exit Sub
        End If


        Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("B5:H5").Borders.LineStyle = xlContinuous
        Range("B5:H5").Borders.Weight = xlThin
        Range("B5:H5").Interior.ColorIndex = 6
        Range("C5:H5").HorizontalAlignment = xlCenter
        Range("B5").HorizontalAlignment = xlLeft

        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

            For i = 1 To 4
                If Me.Controls("OptionButton" & i) = True Then
                    .Range("E5").Value = Me.Controls("OptionButton" & i).Caption
                End If
            Next

        End With

        With Sheets("RANGER")

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

        End With

        Unload RangerForm
        ActiveWorkbook.Save
        MsgBox "DATABASE HAS BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"


        Application.ScreenUpdating = True

        Range("B6").Select
        Range("B5").Select
    End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,909
Office Version
  1. 2007
Platform
  1. Windows
Is this tested ?
I see that i need to add End If but then when i press the transfer button i dont get the msgbox advising optionbutton isnt selected

I have learning issues & this is getting me so confused
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
It is not tested cause I don not have the full issue you have
Now, if possible to upload your file with some data and without confidential data I can have a look
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

End IF
VBA Code:
Private Sub TransferButton_Click()

    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 "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

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

    ElseIf TextBox3.Text = "" Then
        Cancel = 1
        MsgBox "FORD PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox3.SetFocus

    ElseIf TextBox4.Text = "" Then
        Cancel = 1
        MsgBox "ITEM IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox4.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = 1
        MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus

    ElseIf ComboBox2.Text = "" Then
        Cancel = 1
        MsgBox "TYPE IS NOT ENTERED", 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
        End If
    Next
    If x = 0 Then
        MsgBox "MY PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        Exit Sub
    End If


    Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B5:H5").Borders.LineStyle = xlContinuous
    Range("B5:H5").Borders.Weight = xlThin
    Range("B5:H5").Interior.ColorIndex = 6
    Range("C5:H5").HorizontalAlignment = xlCenter
    Range("B5").HorizontalAlignment = xlLeft

    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

        For i = 1 To 4
            If Me.Controls("OptionButton" & i) = True Then
                .Range("E5").Value = Me.Controls("OptionButton" & i).Caption
            End If
        Next

    End With

    With Sheets("RANGER")

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

    End With

    Unload RangerForm
    ActiveWorkbook.Save
    MsgBox "DATABASE HAS BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"


    Application.ScreenUpdating = True

    Range("B6").Select
    Range("B5").Select
End Sub
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,909
Office Version
  1. 2007
Platform
  1. Windows
Now perfect many thanks for your time
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You are welcome
And thank you for the feedback
Be happy
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
BTW
I reviewed the final code
I think you can get ride of one loop
VBA Code:
Private Sub TransferButton_Click()

    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 "CUSTOMER'S NAME FIELD IS EMPTY", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

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

    ElseIf TextBox3.Text = "" Then
        Cancel = 1
        MsgBox "FORD PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox3.SetFocus

    ElseIf TextBox4.Text = "" Then
        Cancel = 1
        MsgBox "ITEM IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox4.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = 1
        MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus

    ElseIf ComboBox2.Text = "" Then
        Cancel = 1
        MsgBox "TYPE IS NOT ENTERED", 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 "MY PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        Exit Sub
    End If


    Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B5:H5").Borders.LineStyle = xlContinuous
    Range("B5:H5").Borders.Weight = xlThin
    Range("B5:H5").Interior.ColorIndex = 6
    Range("C5:H5").HorizontalAlignment = xlCenter
    Range("B5").HorizontalAlignment = xlLeft
    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
    With Sheets("RANGER")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 5).End(xlUp).Row
        .Range("A4:H" & x).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess
    End With
    Unload RangerForm
    ActiveWorkbook.Save
    MsgBox "DATABASE HAS BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Range("B6").Select
    Range("B5").Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

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