albertc30
Well-known Member
- Joined
- May 7, 2012
- Messages
- 1,091
- Office Version
- 2019
- Platform
- 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.
To make it worse, once userform is submitted, and all is correct the data is saved but I get the message customer already exists!?!
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.
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.