Disable MsgBox message

ipbr21054

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

CountTepes

Board Regular
Joined
Nov 8, 2010
Messages
144
Office Version
365, 2016
Platform
Windows
The test for an empty box is done with this code.
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
The following ElseIf checks for duplicates, which if you allow empty value of invoice number you will get. So you still need to test for a zero value, however, you don't need to do any thing about it.

So the snippet above, only needs changing to:
VBA Code:
        If Len(Me.ComboBox13.Text) = 0 Then Exit Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,031
Hi,

So this is what i now have.

VBA Code:
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 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
        ElseIf IsNumeric(Me.ComboBox13.Text) Or Me.ComboBox13.Text = "N/A" Then
I have added Exit Sub on the line as you mentioned but when i leave the field empty & close for i see a message Else without If and when i debug this line below is then in yellow

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,031
Hi,
I see the same message as mentioned about even if i enter a made up invoice number of 91919191
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,747
Office Version
365, 2010
Platform
Windows, Mobile
If I am reading your question correctly then it is just...
VBA Code:
Private Sub CommandButton1_Click()
    With Sheets("DATABASE")
        If Len(Me.ComboBox13.Text) = 0 Then
            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" The
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,031
@MARK858 with that code when i click my button which should transfer userform data to worksheet nothing at all happens,no message etc and userform stoill open.
Let me explain again just in case.

With the original current code in use if i leave ComboBox 13 empty then use my command button i see the msgbox YOU MUST ENTER AN INVOICE NUMBER.

If i enter say 100 and use the command button it checks the worksheet if 100 has allready been used.
If no the userform info is sent to the worksheet.
But if invoice 100 has been used then i see the msgbox INVOICE NUMBER 44 ALLREADY EXISTS.

The above was the original workings of the code.

The edit required is to be able to leave the combobox 13 if i wish & not see the msgbox YOU MUST ENTER AN INVOICE NUMBER.

If an invoice number is entered the check to see if it exists on my worksheet must still continue to work.

Basically allow the user to leave combobox13 empty if they wish

Thanks
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,747
Office Version
365, 2010
Platform
Windows, Mobile
The edit required is to be able to leave the combobox 13 if i wish & not see the msgbox YOU MUST ENTER AN INVOICE NUMBER.
Then just comment out the message box line in your original code if you don't want the message box to appear but I don't understand what you mean by "if i wish".
Do you want the message box to appear sometimes? if yes what is the rule for when it should appear or not?

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,031
Regards entering an Invoice number or not i should not see any msgbox.

The only time a msgbox should be shown is when an invoice is entered but duplicated.
If invoice 123 is in use on my worksheet & a user enters 123 on the userform then show the msgbox.
If invoice 123 is entrered on the userform BUT not on my worksheet then continue to transfer data from userform to worksheet
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
603
If I am reading your original question correct then You don't want a message popping up if ComboBox13 is empty,

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.

if that is so then remove the if = 0

or comment this out

'MsgBox "You Must Enter An Invoice Number", vbCritical, "INVOICE NUMBER FIELD EMPTY MESSAGE"

I did this and it worked

VBA Code:
[/COLOR]
If Len(Me.TextBoxPaste.Value) = 0 Then
            With Me.TextBoxPaste
                 'MsgBox "You Must Copy some URLs to paste", vbCritical, "ERROR"
                .Value = "": .SetFocus
            End With
            Exit Sub
[COLOR=rgb(0, 0, 0)]
The rest of the code should run to check the duplication, it is the same as what Mark858 stated
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,747
Office Version
365, 2010
Platform
Windows, Mobile
@Sharid, If you want to manually color a line or text in XL2BB (as it appears you are trying to trying to do in post #9) use the <rich/> icon rather then the <vba/> icon or the Rich (BB code) option in the dropdown if you are using the </> icon (manually [CODE=rich]YOUR code[/CODE] then select the code to be colored and manually format it) ;)
 
Last edited:

Forum statistics

Threads
1,085,758
Messages
5,385,713
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top