Returning msgbox error even though there ain't one set?!

albertc30

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
Hi all.

The code bellow checks customers sheet for a customers name then if it already exists, then it returns a message and then customer textbox is cleared and set focus to.

however, the focus is not working but instead it's focused on the next textbox.


Code: 
Private Sub tbnbna_AfterUpdate()If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) > 0 Then
MsgBox "Customer already exists!"
Me.tbnbna.Value = ""
Me.tbnbna.SetFocus
    Exit Sub 
    End If   
        adcubton.Enabled = True   
End Sub


To make it worse, once userform is submitted, and all is correct the data is saved but I get the message customer already exists!?!

Code: 
Private Sub adcubton_Click()
    
    If tbnbna.Value = "" Then  'Validates nusiness name
        MsgBox ("Sorry, you need to provide a business name.")
        tbnbna.SetFocus
        Exit Sub
    
        Else
    If tbncn.Value = "" Then     'Validates nusiness contact name
        MsgBox ("Sorry, you need to provide a business contact name.")
        tbncn.SetFocus
        Exit Sub
    
        Else
    If tbnecuadd.Value = "" Then     'Validates business address
        MsgBox ("Sorry, you need to provide a business address.")
        tbnecuadd.SetFocus
        Exit Sub
    
        Else
    If tbncpc.Value = "" Then     'Validates business post code
        MsgBox ("Sorry, you need to provide a business post code.")
        tbncpc.SetFocus
        Exit Sub
    
        Else
    If cbcounty.Value = "" Then 'Validates county post code
        MsgBox ("Sorry, you need to select a county from the dropdown list.")
        cbcounty.SetFocus
        Exit Sub
    
        Else
    If cbcity.Value = "" Then   'Validates town/city post code
        MsgBox ("Sorry, you need to select a town/city from the dropdown list.")
        cbcity.SetFocus
        Exit Sub


        Else
    If tbncem.Value = "" Then   'Validates email post code
        MsgBox ("Sorry, you need to provide an e-mail address.")
        tbncem.SetFocus
        Exit Sub


        Else
    If tbnctel.Value = "" Then 'At least one contact number must de added.
    
    If tbncmob.Value = "" Then
    
    If tbncfax.Value = "" Then
    
        MsgBox ("Sorry, but you must enter at least one form of contact number.")
        tbnctel.SetFocus
        Exit Sub
    
        Else
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
         'Sheets("cust-test").Unprotect Password:="test" 'UnProtects the Invoice sheet.
         Sheets("customers").Unprotect Password:="test" 'UnProtects the Invoice sheet.
    
   
            
    Dim lRow As Long
    Dim ws As Worksheet
        Set ws = Worksheets("customers")
        lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 9).Value = Me.cbcusid.Value    'Copy input values to sheet.
        .Cells(lRow, 1).Value = Me.tbnbna.Value
        .Cells(lRow, 12).Value = Me.tbncn.Value
        .Cells(lRow, 2).Value = Me.tbnecuadd.Value
        .Cells(lRow, 3).Value = Me.tbncpc.Value
        .Cells(lRow, 5).Value = Me.cbcounty.Value
        .Cells(lRow, 4).Value = Me.cbcity.Value
        .Cells(lRow, 6).Value = Me.tbnctel.Value
        .Cells(lRow, 13).Value = Me.tbncmob.Value
        .Cells(lRow, 7).Value = Me.tbncfax.Value
        .Cells(lRow, 8).Value = Me.tbncem.Value
   
    End With
    
            
        Me.cbcusid.Value = ""   'Clear input controls.
        Me.tbnbna.Value = ""
        Me.tbncn.Value = ""
        Me.tbnecuadd.Value = ""
        Me.tbncpc.Value = ""
        Me.cbcity.Value = ""
        Me.cbcounty.Value = ""
        Me.tbnctel.Value = ""
        Me.tbncmob.Value = ""
        Me.tbncfax.Value = ""
        Me.tbncem.Value = ""
          
          
          
                            'code to auto creat user id
    
    Dim LstRow As Long 'Figure out Last Value in Range
    Dim OVal As String 'Old Value
    Dim NVal As String 'New Value
    Dim wsh As Worksheet
        Set wsh = Worksheets("customers")
    


        With wsh
            If .Range("I2") = "" Then
                .Range("I2") = "LMMR0001"
                    Else
                LstRow = .Cells(Rows.Count, "I").End(xlUp).Row
                OVal = .Range("I" & LstRow)
                NVal = "LMMR" & Format(Right(OVal, 4) + 1, "0000")
                Me.cbcusid.Value = NVal
            End If
        End With
    
            'testing code to auto creat user id
            
             tbcuscount.Text = Worksheets("Customers").Range("N1")
          
          
  Sheets("customers").Protect Password:="test" 'Protects the Invoice sheet.
   
        ActiveWorkbook.Save
          
          adcubton.Enabled = False
          
          Me.tbnbna.SetFocus 'I now know that it's because of this code here, that it's bringing up the msgbox message.
          
End Sub

or...

Would it be better practice to put data validation upon executing form by clicking the command button to save data?

Much appreciated for all your help as it's truly appreciated.

Cheers.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Try using an Exit event rather than the AfterUpdate event
Code: 
Private Sub tbnbna__Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) > 0 Then
   MsgBox "Customer already exists!"
   Me.tbnbna.Value = ""
   Cancel = True
   Exit Sub
End If
        adcubton.Enabled = True
End Sub
 
Upvote 0
Hi Fluff.

It does work to an extent.

However, if I accidentally move cursor to next tab, I now get error message customer already exists and it wont allow me to click exit the form as it will display the same message code and set focus to business name.

Gotta love this programming.

I'll never amount to more than getting code from online and change it to what I need.

Much appreciated for your help.

Cheers.
 
Upvote 0
In that case add this line
Code: 
Private Sub tbnbna__Exit(ByVal Cancel As MSForms.ReturnBoolean)
[COLOR=#ff0000]If tbnbna = "" Then Exit Sub[/COLOR]
If Application.WorksheetFunction.CountIf(Range("A:A"), Me.tbnbna) > 0 Then
   MsgBox "Customer already exists!"
   Me.tbnbna.Value = ""
   Cancel = True
   Exit Sub
End If
        adcubton.Enabled = True
End Sub
 
Upvote 0
Hi all again.

When I submit form with new customer I am getting an error which is triggered when the new customer textbox is cleared.


Code: 
Private Sub adcubton_Click()
    
    If tbnbna.Value = "" Then  'Validates nusiness name
        Beep
        MsgBox ("Sorry, you need to provide a business name.")
        tbnbna.SetFocus
        adcubton.Enabled = False
        Exit Sub
    
        Else
    If tbncn.Value = "" Then     'Validates nusiness contact name
        Beep
        MsgBox ("Sorry, you need to provide a business contact name.")
        tbncn.SetFocus
        Exit Sub
    
        Else
    If tbnecuadd.Value = "" Then     'Validates business address
        Beep
        MsgBox ("Sorry, you need to provide a business address.")
        tbnecuadd.SetFocus
        Exit Sub
    
        Else
    If tbncpc.Value = "" Then     'Validates business post code
        Beep
        MsgBox ("Sorry, you need to provide a business post code.")
        tbncpc.SetFocus
        Exit Sub
    
        Else
    If cbcounty.Value = "" Then 'Validates county post code
        Beep
        MsgBox ("Sorry, you need to select a county from the dropdown list.")
        cbcounty.SetFocus
        Exit Sub
    
        Else
    If cbcity.Value = "" Then   'Validates town/city post code
        Beep
        MsgBox ("Sorry, you need to select a town/city from the dropdown list.")
        cbcity.SetFocus
        Exit Sub


        Else
    If tbncem.Value = "" Then   'Validates email post code
        Beep
        MsgBox ("Sorry, you need to provide an e-mail address.")
        tbncem.SetFocus
        Exit Sub


        Else
    If tbnctel.Value = "" Then 'At least one contact number must de added.
    
    If tbncmob.Value = "" Then
    
    If tbncfax.Value = "" Then
    
        Beep
        MsgBox ("Sorry, but you must enter at least one form of contact number.")
        tbnctel.SetFocus
        Exit Sub
    
        Else
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
         Sheets("customers").Unprotect Password:="*******" 'UnProtects the Invoice sheet.
            
    Dim lRow As Long
    Dim ws As Worksheet
        Set ws = Worksheets("customers")
        lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 9).Value = Me.cbcusid.Value    'Copy input values to sheet.
        .Cells(lRow, 1).Value = Me.tbnbna.Value
        .Cells(lRow, 12).Value = Me.tbncn.Value
        .Cells(lRow, 2).Value = Me.tbnecuadd.Value
        .Cells(lRow, 3).Value = Me.tbncpc.Value
        .Cells(lRow, 5).Value = Me.cbcounty.Value
        .Cells(lRow, 4).Value = Me.cbcity.Value
        .Cells(lRow, 6).Value = Me.tbnctel.Value
        .Cells(lRow, 13).Value = Me.tbncmob.Value
        .Cells(lRow, 7).Value = Me.tbncfax.Value
        .Cells(lRow, 8).Value = Me.tbncem.Value
    End With


        Me.cbcusid.Value = ""   'Clear input controls.
[COLOR=#ff0000]        Me.tbnbna.Value = ""    'this line here is triggering the error...[/COLOR]
        Me.tbncn.Value = ""
        Me.tbnecuadd.Value = ""
        Me.tbncpc.Value = ""
        Me.cbcity.Value = ""
        Me.cbcounty.Value = ""
        Me.tbnctel.Value = ""
        Me.tbncmob.Value = ""
        Me.tbncfax.Value = ""
        Me.tbncem.Value = ""
        
                            'code to auto creat user id
    
    Dim LstRow As Long 'Figure out Last Value in Range
    Dim OVal As String 'Old Value
    Dim NVal As String 'New Value
    Dim wsh As Worksheet
        Set wsh = Worksheets("customers")
    
        With wsh
            If .Range("I2") = "" Then
                .Range("I2") = "LMMR0001"
                    Else
                LstRow = .Cells(Rows.Count, "I").End(xlUp).Row
                OVal = .Range("I" & LstRow)
                NVal = "LMMR" & Format(Right(OVal, 4) + 1, "0000")
                Me.cbcusid.Value = NVal
            End If
        End With
                            'testing code to auto creat user id
     
             tbcuscount.Text = Worksheets("Customers").Range("N1")
          
        Sheets("Customers").Cells(LstRow, "U").Value = Sheets("Invoice").Range("CurrentUser").Value 'COPY ACTIVE USER MAKING CHANGES
        Sheets("Customers").Cells(LstRow, "V").Value = " " & Format(Now, "dd/mm/yyyy hh:mm")
               
  Sheets("customers").Protect Password:="*******" 'Protects the Invoice sheet.
   
        ActiveWorkbook.Save
          
          adcubton.Enabled = False
       
End Sub


I believe this is due to the code bellow;

Code: 
Private Sub tbnbna_Change()
     tbnbna.Text = UCase(tbnbna.Text)
     
        'done the bellow
     
     Dim strStrings As String, LastLetter As String


    Application.EnableEvents = False
    LastLetter = Right(tbnbna, 1)
    strStrings = "\/~`|@#$%^&*()_+!<>?:;'"""
    If InStr(1, strStrings, LastLetter) > 0 Then
[COLOR=#ff0000]    MsgBox LastLetter & " not allowed"  'this is the error that is displayed...[/COLOR]
    'tbnbna = Left(tbnbna, Len(tbnbna) - 1)
    tbnbna.Text = ""
    
    Exit Sub
    
    End If
    Application.EnableEvents = True
     
        'done the above
     
End Sub

I am breaking a sweat with this but loving it.

Again, I'm wondering if the validation of the textbox new customer would be better put on submission of the form?

Many thanks.
 
Upvote 0
As this is a totally different question you will need to start a new thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Slight edit to existing working code
Replies
3
Views
598
Yongle
Yongle
I
  • Question Question
Run time error 381 as opposed msgbox message
Replies
6
Views
918
DanteAmor
DanteAmor
W
  • Locked
  • Question Question
Help with Run-time Error '91' with code
Replies
1
Views
495
Fluff
Fluff
L
  • Question Question
Clean Up Code
Replies
1
Views
608
Legacy 436357
L
M
  • Question Question
Help with VBA Code - need answer not in MsgBox but into a Cell - pullin' my last two hairs off my head
Replies
2
Views
1K
Mark_Weir
M

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

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