Show MsgBox if OptionButton not selected

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,096
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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
 

ipbr21054

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

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,096
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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
.
.
.
.
.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,096
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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 ?
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Yes of course
Put it just before this line
VBA Code:
With ThisWorkbook.Worksheets("RANGER")
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,096
Office Version
  1. 2007
Platform
  1. Windows
That shows the msgbox advising of it but then a blank row is inserted into the worksheet
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
OOPs
see x=0 not x=false
 

Forum statistics

Threads
1,147,818
Messages
5,743,382
Members
423,792
Latest member
travisds

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