Disable MsgBox message

ipbr21054

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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
 
Upvote 0
Hi,
I see the same message as mentioned about even if i enter a made up invoice number of 91919191
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
@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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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