Having problems with userform setfocus after enter.
Scenario
userform textbox1 - textbox5 user enters data on enter of textbox6 a validation (i think this is what it's call) is ran. if the validation is not true then userform2 is opened and users enters data and that data is then sent to a works sheet USERFORM2 is unloaded and Userform1 is shown. and finishes the process. the problem is that the "focus" is now textbox 5 and the user then hits tabkey the focus jumps to box7.
i have this as onenter bacause i cant find an onexit process. i would like to actuily run the validation code onexit of textbox5 that way the focus will be on textbox6 on return of validation .
i tried this suggestion but it did not work Excel userform - setting focus of textbox on exit if condition not met
here is the validation code
userform 2 is simple update worksheet with missing data... here is the code if needed for userform2 data fill to worksheet..
Scenario
userform textbox1 - textbox5 user enters data on enter of textbox6 a validation (i think this is what it's call) is ran. if the validation is not true then userform2 is opened and users enters data and that data is then sent to a works sheet USERFORM2 is unloaded and Userform1 is shown. and finishes the process. the problem is that the "focus" is now textbox 5 and the user then hits tabkey the focus jumps to box7.
i have this as onenter bacause i cant find an onexit process. i would like to actuily run the validation code onexit of textbox5 that way the focus will be on textbox6 on return of validation .
i tried this suggestion but it did not work Excel userform - setting focus of textbox on exit if condition not met
here is the validation code
VBA Code:
'------------------------ code in userform1
Private Sub Embossed_Code_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
If boolEnter = True Then
With Embossed_Code
.SelStart = 0
.SelLength = Len(.Text)
End With
boolEnter = False
End If
End Sub
Private Sub Embossed_Code_Enter()
Dim LS As String
Dim LC As String
Dim CA As String
Dim LI As String
Dim LO As String
Dim Cart As String
Dim slabel As String
Dim ws As Worksheet
Dim answer As Integer
Set ws = Worksheets("MASTER_LIST")
boolEnter = True
LS = Label_System.Value
LC = Label_Code.Value
CA = Label_Country.Value
LI = Label_Id.Value
LO = Label_Other.Value
'example LS-LC-CA jpn2
If CA = "-" And LI = "" And LO = "" Then
Cart = LS & "-" & LC
End If
'example LS-LC-CA-LI jpn2
If CA = "-" And LI <> "" And LO = "" Then
Cart = LS & "-" & LC & "--" & LI
End If
'example LS-LC-CA-LI-LO jpn2
If CA = "-" And LI <> "" And LO <> "" Then
Cart = LS & "-" & LC & "--" & LI & "-" & LO
End If
'example LS-LC-CA--LO jpn2
If CA = "-" And LI = "" And LO <> "" Then
Cart = LS & "-" & LC & "---" & LO
End If
'example LS-LC-CA WORLD
If CA <> "-" And LI = "" And LO = "" Then
Cart = LS & "-" & LC & "-" & CA
End If
'example LS-LC-CA-LI WORLD
If CA <> "-" And LI <> "" And LO = "" Then
Cart = LS & "-" & LC & "-" & CA & "-" & LI
End If
'example LS-LC-CA-LI-LO WORLD
If CA <> "-" And LI <> "" And LO <> "" Then
Cart = LS & "-" & LC & "-" & CA & "-" & LI & "-" & LO
End If
'example LS-LC-CA--LO WORLD
If CA <> "-" And LI = "" And LO <> "" Then
Cart = LS & "-" & LC & "-" & CA & "--" & LO
End If
'------------- validation section --------------
slabel = Application.IfError(Application.VLookup(Cart, ws.Range("AC:AI"), 7, 0), "")
'------------- if error section ------------------
If slabel = "" Then
TextBox9.Value = Cart
answer = MsgBox(Cart & " not found would you like to add to Master?", vbCritical + vbYesNo, "Not Found")
'If answer = vbNo Then
'Exit Sub
'End If
If answer = vbYes Then
UserForm1.Hide
UserForm2.show
End If
End If
'----------- update textbox 9 after return from correction of missing data --------------
slabel = Application.IfError(Application.VLookup(Cart, ws.Range("AC:AI"), 7, 0), "")
TextBox9 = slabel
End Subuserform 2 is simple update worksheet with missing data... here is the code if needed for userform2 data fill to worksheet..
VBA Code:
Private Sub Fill_Master_Data()
Dim ws As Worksheet
Dim emptyrow As Integer
Set ws = Worksheets("MASTER_LIST")
ws.Activate
emptyrow = TextBox8.Value
Cells(emptyrow, 13).Value = UserForm1.Label_System.Value '= System.Value ' 'm Name
Cells(emptyrow, 14).Value = UserForm1.Label_Code.Value '= Code.Value 'n Name
Cells(emptyrow, 15).Value = UserForm1.Label_Country.Value '= Country.Value ' 'o Name
Cells(emptyrow, 16).Value = UserForm1.Label_Id.Value ' = ID.Value ' 'p Name
Cells(emptyrow, 17).Value = UserForm1.Label_Other.Value 'q Name
Cells(emptyrow, 23).Value = Name_Box.Value 'w Name
Cells(emptyrow, 1).Select
'Range(emptyrow).EntireRow.Calculate
ActiveCell.EntireRow.Calculate
Unload UserForm2
'UserForm1.Embossed_Code.SetFocus
UserForm1.show
End Sub
