Excel VBA Keep Focus on TextBox

[hendyhellen]

Hello all,

How to make the cursor always focus on the textbox after user pressing enter on textbox ?

Using this code, the cursor not focus on textbox after input and pressing enter

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) <> 0 Then
    Product = UCase(TextBox1.Value)
    Call Print_Label
    Me.TextBox1.Text = vbNullString
    Me.TextBox1.SetFocus
    Cancel = True
End If
End Sub

Thank you all~

 

[Micron]

Did you try the edited code I posted? AFAIK, if you cancel the event, focus should stay where it is. If it doesn't work for you and the file you posted is a copy of the file you're working with, did you check the copy to ensure this problem was also in the copy?

If the situation was that this copy behaved the same way for me, I might be able to help. Since it's not failing for me I can't suggest anything else.

 

[hendyhellen]

Yes, i already replaced the code with this one

I will try and check on another computer
I work with the same file that i posted at the moment

Thank you so much for your help, i will inform you if i can resolve this issue on my side

        Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) <> 0 Then
Product = UCase(TextBox1.Value)
Call Print_Label
Me.TextBox1.Text = vbNullString
'Me.TextBox1.SetFocus
Cancel = True
End If
End Sub

Did you try the edited code I posted? AFAIK, if you cancel the event, focus should stay where it is. If it doesn't work for you and the file you posted is a copy of the file you're working with, did you check the copy to ensure this problem was also in the copy?

If the situation was that this copy behaved the same way for me, I might be able to help. Since it's not failing for me I can't suggest anything else.

 

[Micron]

What about the suggestion in post 3 to make the textbox the active control?

 

[hendyhellen]

Didn't work on my side too for post in #3

I will send you the video that is shown my problem
and here is the updated file

Updated File
Video

Please check if i make the mistakes
I need the cursor always focus on the textbox, because I always doing continuous scan for many items on the warehouse.
So i can always focus on the barcode reader without click on textbox

Thank you

What about the suggestion in post 3 to make the textbox the active control?

 

[Micron]

I can replicate the issue if I allow your printout sub to run (but I have to disconnect from my printer). I think your problem is due to several factors:
- beforeupdate isn't the best event for this. I'd either use command button click (which you don't have) or textbox afterupdate.
- when you set tb to vbNullString, the event runs again. Now that the control contains no value, this
If Len(TextBox1.Text) <> 0 Then
causes the rest of the code to be skipped. At least that's my theory for now. I'm going to move stuff around and test. BTW, Me is an invalid reference in a standard module:
Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub
Also, you don't need a module for every procedure. Is there a reason why you don't just have the printout somewhere in the userform code? Unless you plan to call the printout sub from other form actions, it doesn't need to be on its own either. Code is easier to work with if it's consolidated more.

 

[hendyhellen]

Hello Micron,

Sorry for my bad code, I just learn VBA a week ago and I think my foundation is wrong.
Just learning from the google.

So, could you help me to repair the code ?

My plan is just make the simple code for warehousing purpose
Textbox -> for input using barcode scanner
then VLOOKUP the data that match with the QR Code and printout the details

Thank you so much for your help
Appreciate your desire to help me and others who struggle with VBA code~

I can replicate the issue if I allow your printout sub to run (but I have to disconnect from my printer). I think your problem is due to several factors:
- beforeupdate isn't the best event for this. I'd either use command button click (which you don't have) or textbox afterupdate.
- when you set tb to vbNullString, the event runs again. Now that the control contains no value, this
If Len(TextBox1.Text) <> 0 Then
causes the rest of the code to be skipped. At least that's my theory for now. I'm going to move stuff around and test. BTW, Me is an invalid reference in a standard module:
Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub
Also, you don't need a module for every procedure. Is there a reason why you don't just have the printout somewhere in the userform code? Unless you plan to call the printout sub from other form actions, it doesn't need to be on its own either. Code is easier to work with if it's consolidated more.

 

[Micron]

Now that I've moved your code around there is code running that wasn't before. Result is that I now have no application window and if I close the form, no easy way to expose excel app window. Are you really wanting to hide the app window?
It did work for me now, but I want to experiment to see if the change is due to one or a combination of changes. Going to be a bit of a pain having to go to Task Manager to shut down Excel and maybe lose changes I made. I'll try to show the app window first but don't know how.

 

[hendyhellen]

at my side the app window is open in front of the excel worksheet, still the same like before nothing change

sorry for the trouble cause on your side
Thank you so much~
You've been really helpful for me

Now that I've moved your code around there is code running that wasn't before. Result is that I now have no application window and if I close the form, no easy way to expose excel app window. Are you really wanting to hide the app window?
It did work for me now, but I want to experiment to see if the change is due to one or a combination of changes. Going to be a bit of a pain having to go to Task Manager to shut down Excel and maybe lose changes I made. I'll try to show the app window first but don't know how.

 

[Akuini]

I would like to make the textbox always focus after pressing enter, so after scanning the barcode i don't need to click the mouse again on the textbox
SCAN -- PRINT --- SCAN -- PRINT

Could you explain what you're trying to do in more detail?
I'm not familiar with scanning barcode in Excel.
When you scanning the barcode, where the barcode should go? in the textbox? and in order to do that you need the focus at the textbox first?

 

[Micron]

In a test copy of your file try this:
- delete Module1, 3 and 4
- change Module2 to:
Option Explicit '<< don't duplicate this line anywhere

Public Product As String

Sub Print_Label()

On Error GoTo ErrHandler
With Sheets("Label")
    .Range("A4") = Product
    .PrintOut
    .PrintOut
End With

Exit Sub
ErrHandler:
MsgBox "BARCODE DOES NOT EXIST", vbInformation, ""

End Sub

In ThisWorkbook module at the top:
Option Explicit

In same module, create ThisWorkbook.Open event and paste in:

Private Sub Workbook_Open()
    'Application.Visible = False '<< you can cycle this on/off as it suits you
    UserForm1.Show vbModeless
End Sub

Comment out all the Textbox1 BeforeUpdate code, or delete the whole sub
Add CommandButton1.click event, then paste this code into it:

If Len(TextBox1.Text) <> 0 Then
     Product = UCase(TextBox1.Value)
     Print_Label
     Me.TextBox1.Text = "" 'vbNullString
     Cancel = True
End If

Me.TextBox1.SetFocus

That works for me.
The reason you see a different view is because as I noted, not all of your code runs for you the way you have it.