I can not solve this one (Userform Focus)

[johnsonk]

Hi, I have a userform with several textboxes and comboboxes some textboxes are populated from a closed workbook (Database) when the one of comboboxes is used. When I scan in the first textbox it is not moving to the next textbox the scanner is set correct the tabs order is set correct now when i run the form in the editor and scan in the first textbox it jumps to the next and works perfect but as soon as I close and reopen the workbook it does not work, this is the only issue I need to resolve now then my project is complete but I can not figure out what the issue is. Has anyone come across this issue before ?

Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Zoom = Int(.Height / Me.Height * 90)
Width = .Width
Height = .Height
End With
With GetObject("P:\Stores\DataBase.xlsm")
ComboBox3.List = .Sheets("ALL").Range("B7:B500").Value
End With
Label2.Visible = False
Label3.Visible = False
Label13.Visible = False
Label13.Visible = False
Label14.Visible = False
Label15.Visible = False
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Label20.Visible = False
Label21.Visible = False
Label22.Visible = False
Label23.Visible = False
Label24.Visible = False
ComboBox2.Visible = False
ComboBox3.Visible = False
TextBox8.Visible = False
TextBox9.Visible = False
TextBox10.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
TextBox18.Visible = False
TextBox19.Visible = False
End Sub
Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Value).Select
    Label2.Visible = True
    ComboBox2.Visible = True
End Sub
Private Sub ComboBox2_Change()
    Label3.Visible = True
ComboBox3.Visible = True

If ComboBox2.Value = "IN" Then
Label14.Visible = True
TextBox9.Visible = True
End If

End Sub
Private Sub ComboBox3_Change()
With GetObject("P:\Stores\DataBase.xlsm")
    TextBox1.Value = .Sheets("ALL").Range("A" & ComboBox3.ListIndex + 7).Value
TextBox2.Value = .Sheets("ALL").Range("C" & ComboBox3.ListIndex + 7).Value
TextBox3.Value = .Sheets("ALL").Range("O" & ComboBox3.ListIndex + 7).Value
TextBox4.Value = .Sheets("ALL").Range("N" & ComboBox3.ListIndex + 7).Value
TextBox5.Value = .Sheets("ALL").Range("T" & ComboBox3.ListIndex + 7).Value
Label9.Visible = True
Label10.Visible = True
ComboBox4.Visible = True
TextBox6.Visible = True

    End With
    Workbooks("DataBase").Close

If TextBox3.Value = "" Then
Label11.Visible = False
Label12.Visible = False
Label18.Visible = False
Label19.Visible = False
Label20.Visible = False
    Label21.Visible = False
    TextBox7.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
    TextBox17.Visible = False
    ComboBox5.Visible = False

TextBox3.Text = "No Promo"

Me.Image1.Picture = LoadPicture("\\Fps16\Stores\PackScanImages\" & TextBox3.Value & ".JPG")
Else

Me.Image1.Picture = LoadPicture("\\Fps16\Stores\PackScanImages\" & TextBox3.Value & ".JPG")

Label11.Visible = True
Label12.Visible = True
Label18.Visible = True
Label19.Visible = True
Label20.Visible = True
    Label21.Visible = True
    TextBox7.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
    TextBox17.Visible = True
    ComboBox5.Visible = True

End If
End Sub
Private Sub ComboBox4_Change()
If ComboBox4 = "SLEEVES" Then
Label13.Visible = True
TextBox8.Visible = True
Else
Label13.Visible = False
TextBox8.Visible = False
End If
End Sub
Private Sub TextBox6_Change()
If TextBox6.Value = "" Then
TextBox10.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
Label15.Visible = False
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Else
If TextBox6.Value = 1 Then
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
Label15.Visible = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Else
If TextBox6.Value = 2 Then
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = False
TextBox13.Visible = False
Label15.Visible = True
Label16.Visible = True
Label17.Visible = False
Label18.Visible = False
Else
If TextBox6.Value = 3 Then
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = False
Label15.Visible = True
Label16.Visible = True
Label17.Visible = True
Label18.Visible = False
Else
If TextBox6.Value = 4 Then
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
Label15.Visible = True
Label16.Visible = True
Label17.Visible = True
Label18.Visible = True
End If
End If
End If
End If
End If
End Sub
Private Sub TextBox10_AfterUpdate()
If TextBox1.Value = "" Or TextBox10.Value = "" Then Exit Sub
If TextBox1.Value = TextBox10.Value Then
TextBox10.BackColor = vbGreen
End If
If TextBox10.BackColor = vbRed Then
Application.Speech.Speak "FAIL"
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"LINE NUMBER: " & ComboBox1.Value & vbNewLine & _
"PRODUCT CODE: " & ComboBox3.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL QTY SELECTED: " & TextBox6.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE 1 Scanned: " & TextBox10.Value

On Error Resume Next
With xOutMail
.To = ""
'.CC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox10.Text = ""
TextBox10.BackColor = &HFFFFFF
End If
End If
End Sub
Private Sub TextBox11_AfterUpdate()
If TextBox1.Value = "" Or TextBox11.Value = "" Then Exit Sub
If TextBox1.Value = TextBox11.Value Then
TextBox11.BackColor = vbGreen
End If
If TextBox11.BackColor = vbRed Then
Application.Speech.Speak "FAIL"
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"LINE NUMBER: " & ComboBox1.Value & vbNewLine & _
"PRODUCT CODE: " & ComboBox3.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL QTY SELECTED: " & TextBox6.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE 1 Scanned: " & TextBox10.Value & vbNewLine & _
"LABEL CODE 2 Scanned: " & TextBox11.Value

On Error Resume Next
With xOutMail
.To = ""
'.CC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox10.Text = ""
TextBox10.BackColor = &HFFFFFF
TextBox11.Text = ""
TextBox11.BackColor = &HFFFFFF
End If
End If
End Sub
Private Sub TextBox12_AfterUpdate()
If TextBox1.Value = "" Or TextBox12.Value = "" Then Exit Sub
If TextBox1.Value = TextBox12.Value Then
TextBox12.BackColor = vbGreen
End If
If TextBox12.BackColor = vbRed Then
Application.Speech.Speak "FAIL"
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"LINE NUMBER: " & ComboBox1.Value & vbNewLine & _
"PRODUCT CODE: " & ComboBox3.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL QTY SELECTED: " & TextBox6.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE 1 Scanned: " & TextBox10.Value & vbNewLine & _
"LABEL CODE 2 Scanned: " & TextBox11.Value & vbNewLine & _
"LABEL CODE 3 Scanned: " & TextBox12.Value

On Error Resume Next
With xOutMail
.To = ""
'.CC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox10.Text = ""
TextBox10.BackColor = &HFFFFFF
TextBox11.Text = ""
TextBox11.BackColor = &HFFFFFF
TextBox12.Text = ""
TextBox12.BackColor = &HFFFFFF
End If
End If
End Sub
Private Sub TextBox13_AfterUpdate()
If TextBox1.Value = "" Or TextBox13.Value = "" Then Exit Sub
If TextBox1.Value = TextBox13.Value Then
TextBox13.BackColor = vbGreen
End If
If TextBox13.BackColor = vbRed Then
Application.Speech.Speak "FAIL"
Dim sPath As String
result = MsgBox("THIS CODE DOES NOT MATCH THE PRICE SHEET", vbOKOnly + vbCritical, "WARNING")
If result = vbOK Then
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim wb As Workbook
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "WARNING" & vbNewLine & vbNewLine & _
"There has been a no match scanning error" & vbNewLine & vbNewLine & _
"LINE NUMBER: " & ComboBox1.Value & vbNewLine & _
"PRODUCT CODE: " & ComboBox3.Value & vbNewLine & _
"PRODUCT DESCRIPTION: " & TextBox2.Value & vbNewLine & _
"LABEL QTY SELECTED: " & TextBox6.Value & vbNewLine & _
"LABEL CODE ON PRICE SHEET: " & TextBox1.Value & vbNewLine & _
"LABEL CODE 1 Scanned: " & TextBox10.Value & vbNewLine & _
"LABEL CODE 2 Scanned: " & TextBox11.Value & vbNewLine & _
"LABEL CODE 3 Scanned: " & TextBox12.Value & vbNewLine & _
"LABEL CODE 4 Scanned: " & TextBox13.Value

On Error Resume Next
With xOutMail
.To = ""
'.CC = ""
.Subject = "Stores label code scanning error"
.Body = xMailBody
.Attacments = ActiveSheet
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing

TextBox10.Text = ""
TextBox10.BackColor = &HFFFFFF
TextBox11.Text = ""
TextBox11.BackColor = &HFFFFFF
TextBox12.Text = ""
TextBox12.BackColor = &HFFFFFF
TextBox13.Text = ""
TextBox13.BackColor = &HFFFFFF

End If
End If
End Sub

 

[johnsonk]

Hi NoSparks, None of the above worked I am so confused.

 

[NoSparks]

With the tab stop order being correct nothing I've suggested should be necessary.
I was hoping to be able to run your program and actually see the focus not going to 11 from 10 unfortunately I can only run the form manually and like you say it works properly that way.
Interesting that the result of post 10 code wasn't either. I didn't think it was possible.

Only other suggestions I have is to test with all textboxes visible all the time and maybe putting a message box in the _Enter event of textbox11 and maybe you'll see if the focus ever goes there.

Sorry I couldn't help you on this. If you ever do figure out why this is happening please report back as it will be a learning experience for me.

Good Luck

 

[johnsonk]

Hi NoSparks, You are not going to believe this but got it to work I have just changed the multiline to true in properties and it now it works fine.
Thank you for all your help with this.

 

[johnsonk]

NoSparks, scrap that worked twice and now it stopped working again

 

[NoSparks]

Confusing for sure.
With no message coming from the post 10 suggestion, I'd have to suspect the focus actually is going to textbox11 before going to textbox12
for test purpose would add this just to see.

Private Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Message from Textbox11.EXIT"
End Sub

 

[johnsonk]

Hi NoSparks, When I scan in the first textbox it looks like it is not exiting but as soon as I click in to the second textbox it exits.

 

[NoSparks]

Why are you showing that for textbox10? We know the focus leaves textbox10.
You say when running the form as you normally would, not manually, the focus jumps from textbox10 to textbox12.

You didn't say you got a message when testing with the code of post 10 so the focus has to should be going to textbox11 in accordance with the tab stop line up.

The intent of putting the message on the exit of textbox11 is that it will be triggered when textbox11 loses focus and would let you know if focus goes to textbox11 before going to textbox12 when it jumps from 10 to 12.

 

[johnsonk]

Hi, Solved the focusing problem, for what ever reason when the userform opened it was setting the enter key behavior to true so all I have done is in userform Initialize put form1.TextBox10.EnterKeyBehavior = False or each textbox and now it works perfect. The problem I am having now is this error

any ideas ?

 

[Rick Rothstein]

If I had to guess, I would say that property is set to True in the Properties Window for that control... you should be able to change it there instead of having to do so in code.

 

[johnsonk]

Rick it is set to false in the properties window.