I can not solve this one (Userform Focus)

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
169
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 ?
1601059143324.png


VBA Code:
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,049
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2019
Messages
169
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.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,049
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
Private Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "Message from Textbox11.EXIT"
End Sub
 

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
169
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.


1601365215003.png


1601365116553.png
 

Attachments

  • 1601365003257.png
    1601365003257.png
    6.7 KB · Views: 1

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,049
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 4, 2019
Messages
169
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
1601487463330.png

any ideas ?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,590
Messages
5,625,674
Members
416,125
Latest member
NeedExcelHelp2021

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
Top