MsgBox advice for two different message options

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows
Morning.
I am using the code supplied below.
The issue i believe is with the code in Red.
The message concenred is in BLUE

This is used on a userform where making a selection from a ComboBox would then show two different selectable options BUT the message shown in BLUE below in shown for BOTH & ideally would like two different messages.

Example

If i select "TEST 1" from the ComboBox & i then see OptionButton1 & OptionButton3 appear.
If i now dont select either & try to continue i see the message shown in RED below.

If i select "TEST 2" from the ComboBox & i then see OptionButton2 & OptionButton4 appear.
If i now dont select either & try to continue i see the message shown in RED below.

So the same message shown for both cases, Ideally would be nice to see message YOU DID NOT SELECT EITHER OPTION 1 OR 3 & another message like YOU DID NOT SELECT EITHER OPTION 2 OR 4
So each have there own message
Rich (BB code):
Private Sub TransferButton_Click()
With ThisWorkbook.Worksheets("RANGER")
    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 "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox1.SetFocus

    ElseIf TextBox2.Text = "" Then
        Cancel = 1
        MsgBox "YOU DIDNT ENTER THE VIN", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        TextBox2.SetFocus

    ElseIf ComboBox1.Text = "" Then
        Cancel = 1
        MsgBox "NO YEAR WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
        ComboBox1.SetFocus

    ElseIf ComboBox2.Text = "" Then
        Cancel = 1
        MsgBox "REMOTE TYPE WAS NOT SELECTED", 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 "YOU DIDNT SELECT AN OPTION BUTTON", vbCritical, "RANGER OPTION BUTTON EMPTY MESSAGE"
        Exit Sub
    End If

    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
    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
        
    If ComboBox2.Value = "ORIGINAL 2B" Then

 Unload RangerFormRemote
 RangerPcbNumber.Show
    
 Else
    With .Range("I5")
        .Value = "N/A"
        .Font.Size = 14
        .Font.Name = "Calibri"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
        Unload RangerFormRemote
     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
        MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
    End With
        Application.ScreenUpdating = True
    Range("B6").Select
    Range("B5").Select
    End If
 End With
 
End Sub
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
Ok, let me double-check before considering that.
With the code in #2, you get the message "NO FINIS NUMBER WAS SELECTED" consistently, or something else?
If something else, what is it?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
Ok, I think I got it.
Try this:
Rich (BB code):
    ElseIf OptionButton5.Value = False And OptionButton6.Value = False And OptionButton5.Visible = True And OptionButton6.Visible = True Then
        Cancel = 1
        MsgBox "NO FINIS NUMBER WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"      
     ElseIf OptionButton2.Value = False And OptionButton4.Value = False And OptionButton2.Visible = True And OptionButton4.Visible = True Then
        Cancel = 1
        MsgBox "NO UPRATED OPTION WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
    End If
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows
Hi,
Yes that now applies the two different Msgbox messages.

BUT

I got a error message, Could not find the specified object this line of code is shown in yellow when i debug.
i Got this error after completing is item then pressing the transfer command button.


Rich (BB code):
.Range("E5").Value = Me.Controls("OptionButton" & Opt).Caption


The values from the userform are placed into the worksheet at transfer time.

Column E5 will be filled depending on what was selected from the OptionButton1,2,3 or 4
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Add the following line
VBA Code:
Debug.Print Opt
after
VBA Code:
    x = 0
    For i = 1 To 4
    If Me.Controls("OptionButton" & i) = True Then
        x = x + 1
        Opt = i
    End If
If 0 is shown in the immediate window you have a problem with the "after" part of the code.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Try this update to the part of the MSGBOX code you want to change & see if does what you want

VBA Code:
Dim msg As String
    msg = Choose(Me.ComboBox2.ListIndex + 1, "1 OR 3", "2 OR 4", "")
    If x = 0 Then
        MsgBox "YOU DID NOT SELECT EITHER OPTION " & msg, vbCritical, "RANGER OPTION BUTTON EMPTY MESSAGE"
        Exit Sub
    End If

I have assumed ComboBox2 is the control that determines which optionbuttons are made visible? - change as required


Dave
 

ipbr21054

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

ADVERTISEMENT

Sorry not sure on that post can you show me it within some of the code so i can see please
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,508
Office Version
  1. 2019
Platform
  1. Windows
Sorry not sure on that post can you show me it within some of the code so i can see please

It replaces the part you marked in RED in your original post

Rich (BB code):
If x = 0 Then
        MsgBox "YOU DIDNT SELECT AN OPTION BUTTON", vbCritical, "RANGER OPTION BUTTON EMPTY MESSAGE"
        Exit Sub
    End If

Dave
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,923
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I didnt contine after post #14
Reason being i saved the worksheet & closed it & then it worked fine.

Many thanks for the help.
Another job now completed.
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
341
Office Version
  1. 365
Platform
  1. Windows
A somewhat compact version:
VBA Code:
Private Sub TransferButton_Click()
    Dim i As Long, x As Long

    With Sheets("RANGER")

        If TextBox1.Value = "" Then
            MsgBox "NO CUSTOMER'S NAME WAS ENTERED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            TextBox1.SetFocus
            Exit Sub
        ElseIf TextBox2.Value = "" Then
            MsgBox "YOU DIDNT ENTER THE VIN", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            TextBox2.SetFocus
            Exit Sub
        ElseIf ComboBox1.Value = "" Then
            MsgBox "NO YEAR WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            ComboBox1.SetFocus
            Exit Sub
        ElseIf ComboBox2.Value = "" Then
            MsgBox "REMOTE TYPE WAS NOT SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            ComboBox2.SetFocus
            Exit Sub
        ElseIf OptionButton5.Value = False And OptionButton6.Value = False And OptionButton5.Visible = True And OptionButton6.Visible = True Then
            MsgBox "NO FINIS NUMBER WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            Exit Sub
        ElseIf OptionButton2.Value = False And OptionButton4.Value = False And OptionButton2.Visible = True And OptionButton4.Visible = True Then
            MsgBox "NO UPRATED OPTION WAS SELECTED", vbCritical, "RANGER FIELD EMPTY MESSAGE"
            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 "YOU DIDNT SELECT AN OPTION BUTTON", vbCritical, "RANGER OPTION BUTTON EMPTY MESSAGE"
            Exit Sub
        End If

        .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").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
       
        If ComboBox2.Value = "ORIGINAL 2B" Then
            Unload RangerFormRemote
            RangerPcbNumber.Show
        Else
       
        With .Range("I5")
            .Value = "N/A"
            .Font.Size = 14
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
            Unload RangerFormRemote
        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
            MsgBox "DATABASE UPDATED SUCCESSFULLY", vbInformation, "SUCCESSFUL MESSAGE"
        End If

    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,378
Messages
5,635,911
Members
416,887
Latest member
SheriE

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