Disable MsgBox message

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
Office Version
  1. 2007
Platform
  1. Windows
Morning,
I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number.

It is this MsgBox i wish to remove so in future the user is able to leave it empty if they wish BUT should they enter an invoice number the code still must check if its beeing duplicated.

Please can you advise what i need to delete so i am able to leave the field empty but still check if the value entered is duplicated then advise me.

This is part of the code we need to edit.

VBA Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub

        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then
 
Sort by date Sort by votes
Here is my code.
VBA Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) Then Exit Sub
            With Me.ComboBox13
                 'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub

I see in your code you have TextBoxPaste but i assume thats your test hence my code above BUT i still see the error message Compile Error Else without IF.
When i debug this line is in yellow.

ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Remove that Exit Sub from
Rich (BB code): 
 If Len(Me.ComboBox13.Text) Then Exit Sub
it is not in either of the last codes I or Sharid posted.
 
Upvote 0
So here is the code with what you advised removed.

I then see the msgbox INVOICE NUMBER ALLREADY EXISTS
This is when nothing is entered in the combobox13

VBA Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) Then
            With Me.ComboBox13
                 'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then
 
Upvote 0
Sorry I removed the = 0 in error in the last message, it should be
VBA Code: 
        If Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
 
Upvote 0
My code is now as follows.

VBA Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub

I enter customer name and leave combobox empty.
I press the command button and i see the combobox has the focus set on it.
Pressing the command button does nothing.
The userform is still open & no msg are shown
 
Upvote 0
Please post your full code as it is probably going to be easier/tidier to just write a
VBA Code: 
If Len(Me.ComboBox13.Text) <> 0
code.
 
Upvote 0
Here it is,

VBA Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then

             Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
             Range("A6").Select
             Range("A6:Q6").Borders.LineStyle = xlContinuous
             Range("A6:Q6").Borders.Weight = xlThin
             Range("A6:Q6").Interior.ColorIndex = 6
             Range("M6") = Date
             Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
             Range("$Q$6").HorizontalAlignment = xlCenter
            
            .Range("A6").Value = Me.TextBox1.Text
            .Range("B6").Value = Me.ComboBox1.Text
            .Range("C6").Value = Me.ComboBox2.Text
            .Range("D6").Value = Me.ComboBox3.Text
            .Range("E6").Value = Me.ComboBox4.Text
            .Range("F6").Value = Me.ComboBox5.Text
            .Range("G6").Value = Me.ComboBox6.Text
            .Range("H6").Value = Me.ComboBox7.Text
            .Range("I6").Value = Me.ComboBox8.Text
            .Range("J6").Value = Me.ComboBox9.Text
            .Range("K6").Value = Me.ComboBox10.Text
            .Range("L6").Value = Me.ComboBox11.Text
            .Range("M6").Value = Me.TextBox2.Text
            .Range("N6").Value = Me.ComboBox12.Text
            .Range("O6").Value = Me.TextBox3.Text
            .Range("P6").Value = Me.ComboBox13.Text
            .Range("Q6").Value = Me.TextBox5.Text
        Else
            With Me.ComboBox13
                MsgBox "Invoice " & .Text & " Is Not A Number", vbCritical, "INVALID INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        End If
    End With
    
    Dim ctrl As MSForms.Control
 
    For Each ctrl In Me.Controls
        Select Case True
            Case TypeOf ctrl Is MSForms.TextBox
                ctrl.Value = ""
            Case TypeOf ctrl Is MSForms.ComboBox
                ctrl.Value = ""
        End Select
    Next ctrl
    
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL TRANSFER MESSAGE"
    Range("A7").Select
    Range("A6").Select
    Unload DatabaseInput
End Sub
Private Sub CommandButton2_Click()
Unload DatabaseInput
Range("B6").Select
Range("A6").Select
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
    TextBox2 = UCase(TextBox2)
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
End Sub
Private Sub TextBox3_Change()
    TextBox3 = UCase(TextBox3)
End Sub
Private Sub TextBox4_Change()
    TextBox4 = UCase(TextBox4)
End Sub

Private Sub UserForm_Initialize()
TextBox2.Value = Format(Date, "dd/mm/yyyy")

Dim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value

'BLANK USED
Dim lastrowl As Long
lastrowl = Sheets("INFO").Cells(Rows.Count, "L").End(xlUp).Row
ComboBox2.List = Sheets("INFO").Cells(2, "L").Resize(lastrowl - 1).Value

'VEHICLE
Dim lastrowb As Long
lastrowb = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowb - 1).Value

'BUTTONS
Dim lastrowj As Long
lastrowj = Sheets("INFO").Cells(Rows.Count, "J").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "J").Resize(lastrowj - 1).Value

'ITEM SUPPLIED
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "T").End(xlUp).Row
ComboBox5.List = Sheets("INFO").Cells(2, "T").Resize(lastrowt - 1).Value

'TRANSPONDER CHIP
Dim lastrowf As Long
lastrowf = Sheets("INFO").Cells(Rows.Count, "F").End(xlUp).Row
ComboBox6.List = Sheets("INFO").Cells(2, "F").Resize(lastrowf - 1).Value

'JOB ACTION
Dim lastrowh As Long
lastrowh = Sheets("INFO").Cells(Rows.Count, "H").End(xlUp).Row
ComboBox7.List = Sheets("INFO").Cells(2, "H").Resize(lastrowh - 1).Value

'PROGRAMMER USED
Dim lastrowd As Long
lastrowd = Sheets("INFO").Cells(Rows.Count, "D").End(xlUp).Row
ComboBox8.List = Sheets("INFO").Cells(2, "D").Resize(lastrowd - 1).Value

'KEY CODE
Dim lastrowp As Long
lastrowp = Sheets("INFO").Cells(Rows.Count, "P").End(xlUp).Row
ComboBox9.List = Sheets("INFO").Cells(2, "P").Resize(lastrowp - 1).Value

'BITING
Dim lastrowx As Long
lastrowx = Sheets("INFO").Cells(Rows.Count, "X").End(xlUp).Row
ComboBox10.List = Sheets("INFO").Cells(2, "X").Resize(lastrowx - 1).Value

'CHASSIS NUMBER
Dim lastrown As Long
lastrown = Sheets("INFO").Cells(Rows.Count, "N").End(xlUp).Row
ComboBox11.List = Sheets("INFO").Cells(2, "N").Resize(lastrown).Value

'VEHCILE YEAR
Dim lastrowv As Long
lastrowv = Sheets("INFO").Cells(Rows.Count, "V").End(xlUp).Row
ComboBox12.List = Sheets("INFO").Cells(2, "V").Resize(lastrowv - 1).Value

'INVOICE NUMBER
Dim lastroww As Long
lastroww = Sheets("INFO").Cells(Rows.Count, "W").End(xlUp).Row
ComboBox13.List = Sheets("INFO").Cells(2, "W").Resize(lastroww).Value

End Sub
 
Upvote 0
I can only get it to work by selecting the N/A in the drop down
 
Upvote 0
You are exiting the subroutine, without unloading the userform. Exit Sub, just exits your routine without executing anymore code in the routine.

If you need to be able to enter the information and save it without necessarily having an invoice number, the logic in your code needs a re-write.

Remove the Exit Sub in the block which checks for invoice>0

All your other ElseIf statements need an invoice value length check

So alter this line
VBA Code: 
ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
to be
VBA Code: 
ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not (Me.ComboBox13.Text = "N/A" or Len(Me.ComboBox13.Text) = 0) Then
and this line
VBA Code: 
ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then
to this
VBA Code: 
ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" or Len(Me.ComboBox13.Text) = 0 Then
 
Upvote 0
CountTepes said:
You are exiting the subroutine, without unloading the userform. Exit Sub, just exits your routine without executing anymore code in the routine.

If you need to be able to enter the information and save it without necessarily having an invoice number, the logic in your code needs a re-write.

Remove the Exit Sub in the block which checks for invoice>0

All your other ElseIf statements need an invoice value length check

So alter this line
VBA Code: 
ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not Me.ComboBox13.Text = "N/A" Then
to be
VBA Code: 
ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not (Me.ComboBox13.Text = "N/A" or Len(Me.ComboBox13.Text) = 0) Then
and this line
VBA Code: 
ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then
to this
VBA Code: 
ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" or Len(Me.ComboBox13.Text) = 0 Then
Click to expand...


Hi,
Do you mean like this ?

VBA Code: 
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            With Me.ComboBox13
                 'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"
                .Value = "": .SetFocus
            End With
            
        ElseIf Application.CountIf(.Columns(16), Me.ComboBox13.Text) > 0 And Not (Me.ComboBox13.Text = "N/A" Or Len(Me.ComboBox13.Text) = 0) Then
            With Me.ComboBox13
                MsgBox "Invoice Number " & .Text & " Already Exists", vbCritical, "DUPLICATE INVOICE NUMBER MESSAGE"
                .Value = "": .SetFocus
            End With
            Exit Sub
        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Or Len(Me.ComboBox13.Text) = 0 Then

             Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
87
ipbr21054
I
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
185
ipbr21054
I
I
  • Solved
Add date to pdf output
Replies
5
Views
85
Joe4
Joe4
I
  • Question
Open selected customer in my main database
Replies
2
Views
160
ipbr21054
I
I
  • Solved
Exclude a row when searching table values
Replies
3
Views
143
mumps
mumps

Forum statistics

Threads
1,214,983
Messages
6,122,582
Members
449,089
Latest member
Motoracer88

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