MsgBox advice for two different message options

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
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
 
Sort by date Sort by votes
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?
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Sorry not sure on that post can you show me it within some of the code so i can see please
 
Upvote 0
ipbr21054 said:
Sorry not sure on that post can you show me it within some of the code so i can see please
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Do calculation & show value in Msgbox
Replies
6
Views
132
RoryA
RoryA
I
  • Solved
Enter row number in listbox1 when value on worksheet found
2
Replies
10
Views
358
ipbr21054
I
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
185
ipbr21054
I
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
128
still learning
S
I
  • Question
Advice for if cell has a value then
Replies
7
Views
270
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,214,929
Messages
6,122,315
Members
449,081
Latest member
tanurai

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