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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
VBA Code: 
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 ""
End If
 
Upvote 0
Hi,
This is the full code & i placed it as shown BUT no msgbox was shown & i was able to continue the still send values from form to worksheet.

Please advise i should correctly place it



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

    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"
End If
    
If Cancel = 1 Then
        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
In this case
VBA Code: 
   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
 
Upvote 0
Morning,
Ive placed it in the code shown below but cant get it to work,i know its in the wrong place but i tried a few different places but still the same.
Please advise correct place for it to work

Thanks for your time.


Rich (BB 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
    Exit Sub
    
ElseIf TextBox2.Text = "" Then
    Cancel = 1
    MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox2.SetFocus
    Exit Sub
    
ElseIf ComboBox1.Text = "" Then
    Cancel = 1
    MsgBox "YEAR IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox1.SetFocus
    Exit Sub
        
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

ElseIf TextBox3.Text = "" Then
    Cancel = 1
    MsgBox "FORD PART NUMBER IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox3.SetFocus
    Exit Sub
    
ElseIf TextBox4.Text = "" Then
    Cancel = 1
    MsgBox "ITEM IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    TextBox4.SetFocus
    Exit Sub
    
ElseIf ComboBox2.Text = "" Then
    Cancel = 1
    MsgBox "TYPE IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    ComboBox2.SetFocus
    Exit Sub

If Cancel = 1 Then
        Exit Sub
End If

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
Ok maybe
VBA Code: 
Private Sub TransferButton_Click()

    Dim i As Long
    Dim x As Long
    Dim ctrl As Control
    Dim lastrow As Long

    x = False
    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

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

    ElseIf TextBox2.Text = "" Then
        Cancel = 1
        MsgBox "VIN FIELD IS NOT ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox2.SetFocus
        Exit Sub
.
.
.
.
.
 
Upvote 0
Hi,
That works BUT can we change the position of the code so it checks its in order of the textbox / combobox on the form ?
 
Upvote 0
Yes of course
Put it just before this line
VBA Code: 
With ThisWorkbook.Worksheets("RANGER")
 
Upvote 0
That shows the msgbox advising of it but then a blank row is inserted into the worksheet
 
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
182
Patriot2879
P
P
  • Question
TextBox to count values from ComboBox item
Replies
1
Views
121
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
457
RainerGaier
R
K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
674
Kakaska
K

Forum statistics

Threads
1,214,565
Messages
6,120,254
Members
448,952
Latest member
kjurney

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