Show MsgBox if OptionButton not selected

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
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
 
Sort by date Sort by votes
Can you please just put the whole code here as im getting confused now, i can then copy & paste
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
You are welcome
And thank you for the feedback
Be happy
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
183
Patriot2879
P
T
  • Question
How to show Msg if Record not found in table
Replies
3
Views
82
myall_blues
myall_blues
P
  • Question
TextBox to count values from ComboBox item
Replies
1
Views
124
Patriot2879
P
I
  • Solved
Do calculation & show value in Msgbox
Replies
6
Views
130
RoryA
RoryA
R
  • Question
LostFocus VBA Form Events not firing and SetFocus method also not working
Replies
2
Views
472
RainerGaier
R

Forum statistics

Threads
1,214,639
Messages
6,120,679
Members
448,977
Latest member
dbonilla0331

Share this page

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
Back
Top