Setfocus in Userform not working...

C

chipsworld

Board Regular
Joined
May 23, 2019
Messages
164
Office Version
  1. 365
Good day all...
I am experiencing something that I do not understand.
I have a simple data entry userform with a few text fields.

The issue is:
Form works great except after hitting the Apply button to run the code to update everything.

The last line of the code is to set the focus back to the first text field to do another item.

When it runs, everything works except for the set focus. I get the below error....

1685637933785.png


The crazy part is...after I hit debug and see that the line highlighted is the cyclecntfrm.upcttxt.SetFocus line. When I then click the run arrow on the ribbon, it works perfectly and sets the focus in the first txt field..

I am at a loss...

HEre is the code and form for that Sub...

the only fields set with a Tab Stop and the "Scan UPC Code" = 0, "Current System Inventory Qty" = 1, and "Correct Inventory Qty" = 2

1685638269267.png



VBA Code: 
Dim user As String
Dim upcnum As String

Private Sub cmdaply_Click()
Dim lkup As String
Dim updwn As Double

lkup = upcnum

        With ThisWorkbook.Sheets("Inventory")
            Dim rw As Long, Fnd As Range
        Set Fnd = .Range("A:D").Find(lkup, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
            If Not Fnd Is Nothing Then
                rw = Fnd.Row
            End If
                
                If (Me.crnttxt.Value = Me.cortxt.Value) Or Me.cortxt.Value = "" Then
                    
                    With ThisWorkbook.Sheets("CycleCount")
                        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row   ' add transaction to "Transaction" sheet
                        rw1 = lastrow + 1
                        
                        'add transaction to CycleCount worksheet
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "A").Value = Me.desctxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "B").Value = Me.crnttxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "C").Value = updwn
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "D").Value = Me.cortxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "E").Value = user
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "F").Value = Now()
                        
                    End With
                    
                Else 'make correction to Inventory sheet
                    updwn = Me.cortxt.Value - Me.crnttxt.Value 'calculate difference
                    
                    ThisWorkbook.Sheets("Inventory").Cells(rw, "G").Value = ThisWorkbook.Sheets("Inventory").Cells(rw, "G").Value + updwn 'calculate new inventory balance
                
                    With ThisWorkbook.Sheets("CycleCount")
                        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row   ' add transaction to "Transaction" sheet
                        rw1 = lastrow + 1
                        
                        'add transaction to CycleCount worksheet
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "A").Value = Me.txtptnum.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "B").Value = Me.desctxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "C").Value = Me.crnttxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "D").Value = updwn
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "E").Value = Me.cortxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "F").Value = user
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "G").Value = Now()
                    End With
                
                End If
        End With
        
        Me.upcttxt.Value = ""
        Me.desctxt.Value = ""
        Me.crnttxt.Value = ""
        Me.cortxt.Value = ""
        Me.txtptnum.Value = ""
        Me.txtuntiss.Value = ""
        ThisWorkbook.Save
        
        
        [B]cyclecntfrm.upcttxt.SetFocus[/B]

        
        
End Sub
 
Sort by date Sort by votes
Akuini said:
Weird, when you put it at the start of Sub and the code stops there, it means it stops before any other action occurs.
Let's try something simple, comment all the lines except cyclecntfrm.upctxt.setfocus, so there's only one line to be executed, see what happens.
Click to expand...
Same thing...even when it is the only line of code in the sub, it stops on it with the same unspecified error. Hit DEBUG and then run arrow in ribbon, and works as it should.
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
chipsworld said:
Same thing...even when it is the only line of code in the sub, it stops on it with the same unspecified error. Hit DEBUG and then run arrow in ribbon, and works as it should.
Click to expand...
Just discovered that if I set the form CYCLE property to 2-CycleCurrentForm instead of 1-frmCycleAllForms

Not sure what this means, but it did the trick.

Disregard...it worked once, then failed after that.
 
Upvote 0
chipsworld said:
Just discovered that if I set the form CYCLE property to 2-CycleCurrentForm instead of 1-frmCycleAllForms

Not sure what this means, but it did the trick.

Disregard...it worked once, then failed after that.
Click to expand...
OK...I figured out what was causing the Unspecific Error but...now the setfocus line does nothing.

Is there a better way to reset the form and set the focus back to the 0-tab position?
 
Upvote 0
Akuini said:
What did you really figured out?

Sorry, I'm not sure how, hope others could help you,
Click to expand...
Akuini,
I ound when stepping through the code, it was going to another control (which it shouldn't). I removed that code, and the error went away.

Been playing with it, and everything works smoothly. Still couldn't get the setfocus to work, but I did manage to get the flow to work right. Just using the tab order. I guess I just had to let it do what it wanted to do.

Thanks for the help!
 
Upvote 0
chipsworld said:
Been playing with it, and everything works smoothly. Still couldn't get the setfocus to work, but I did manage to get the flow to work right. Just using the tab order. I guess I just had to let it do what it wanted to do.
Click to expand...

Ok, glad it worked, even though not completely as you need.
 
Upvote 0
chipsworld said:
Good day all...
I am experiencing something that I do not understand.
I have a simple data entry userform with a few text fields.

The issue is:
Form works great except after hitting the Apply button to run the code to update everything.

The last line of the code is to set the focus back to the first text field to do another item.

When it runs, everything works except for the set focus. I get the below error....

View attachment 92720

The crazy part is...after I hit debug and see that the line highlighted is the cyclecntfrm.upcttxt.SetFocus line. When I then click the run arrow on the ribbon, it works perfectly and sets the focus in the first txt field..

I am at a loss...

HEre is the code and form for that Sub...

the only fields set with a Tab Stop and the "Scan UPC Code" = 0, "Current System Inventory Qty" = 1, and "Correct Inventory Qty" = 2

View attachment 92721


VBA Code: 
Dim user As String
Dim upcnum As String

Private Sub cmdaply_Click()
Dim lkup As String
Dim updwn As Double

lkup = upcnum

        With ThisWorkbook.Sheets("Inventory")
            Dim rw As Long, Fnd As Range
        Set Fnd = .Range("A:D").Find(lkup, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
            If Not Fnd Is Nothing Then
                rw = Fnd.Row
            End If
               
                If (Me.crnttxt.Value = Me.cortxt.Value) Or Me.cortxt.Value = "" Then
                   
                    With ThisWorkbook.Sheets("CycleCount")
                        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row   ' add transaction to "Transaction" sheet
                        rw1 = lastrow + 1
                       
                        'add transaction to CycleCount worksheet
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "A").Value = Me.desctxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "B").Value = Me.crnttxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "C").Value = updwn
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "D").Value = Me.cortxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "E").Value = user
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "F").Value = Now()
                       
                    End With
                   
                Else 'make correction to Inventory sheet
                    updwn = Me.cortxt.Value - Me.crnttxt.Value 'calculate difference
                   
                    ThisWorkbook.Sheets("Inventory").Cells(rw, "G").Value = ThisWorkbook.Sheets("Inventory").Cells(rw, "G").Value + updwn 'calculate new inventory balance
               
                    With ThisWorkbook.Sheets("CycleCount")
                        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row   ' add transaction to "Transaction" sheet
                        rw1 = lastrow + 1
                       
                        'add transaction to CycleCount worksheet
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "A").Value = Me.txtptnum.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "B").Value = Me.desctxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "C").Value = Me.crnttxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "D").Value = updwn
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "E").Value = Me.cortxt.Value
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "F").Value = user
                        ThisWorkbook.Sheets("CycleCount").Cells(rw1, "G").Value = Now()
                    End With
               
                End If
        End With
       
        Me.upcttxt.Value = ""
        Me.desctxt.Value = ""
        Me.crnttxt.Value = ""
        Me.cortxt.Value = ""
        Me.txtptnum.Value = ""
        Me.txtuntiss.Value = ""
        ThisWorkbook.Save
       
       
        [B]cyclecntfrm.upcttxt.SetFocus[/B]

       
       
End Sub
Click to expand...
Where is the Form loaded?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Printing to PDF use VBA - Was working but now different sheets are different widths
Replies
1
Views
210
Tanquen
T
Ottsel
  • Question Question
Create & Secure PDFS with Excel
Replies
3
Views
192
John_w
J
ExcelNewbie2020
  • Solved
VBA to replicate values to allign with the longest non empty cell in other columns
Replies
1
Views
200
Eric W
Eric W
V
  • Question Question
autofilter
Replies
2
Views
323
vmjan02
V
U
  • Question Question
Text Value from Textbox to Integer
Replies
1
Views
334
Kaper
Kaper

Forum statistics

Threads
1,223,957
Messages
6,175,623
Members
452,661
Latest member
Nonhle

Share this page

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
Back
Top