Setfocus in Userform not working...

[chipsworld]

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....

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

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

 

[NdNoviceHlp]

Hi chipsworld. Seems puzzling. Maybe it has to do with the wb save before the set focus? Does it matter if you do the save last. Maybe..

Me.upcttxt.Value = ""
cyclecntfrm.upcttxt.SetFocus
ThisWorkbook.Save

I assume that Me and cyclecntfrm are the same. HTH. Dave

 

[chipsworld]

Hi chipsworld. Seems puzzling. Maybe it has to do with the wb save before the set focus? Does it matter if you do the save last. Maybe..

Me.upcttxt.Value = ""
cyclecntfrm.upcttxt.SetFocus
ThisWorkbook.Save

I assume that Me and cyclecntfrm are the same. HTH. Dave

Dave,
Yes... I have tried it with and without the WB save and the result is the same.
I have even tried using a separate sub to do the setfocus, same issue.
Yes... Cyclecntfrm is the name of the form.

I have also tried putting a time delay between the WB save and the set focus. This gave me the same issue as well.

Very frustrating!

 

[NdNoviceHlp]

One last try that has helped me solve the unexplained before. Before the set focus...

Dim t As Double
t = Timer
Do Until Timer - t > 1
  DoEvents
Loop

Beyond this, I'm afraid that I'm out of suggestions. Good luck. Dave

 

[chipsworld]

One last try that has helped me solve the unexplained before. Before the set focus...

Dim t As Double
t = Timer
Do Until Timer - t > 1
  DoEvents
Loop

Beyond this, I'm afraid that I'm out of suggestions. Good luck. Dave

Dave,
Thanks for the try. Unfortunately, I get the same error.

I did have a question however...

I have read that when the code interreacts with the sheet, the focus is temporarily moved to the sheet. Could I somehow move the focus back to the form and control using "Activecontrol"? Have never used this before.

 

[Akuini]

@chipsworld
If you put this cyclecntfrm.upcttxt.SetFocus in the beginning of the Sub, say before lkup = upcnum, does it work?

 

[chipsworld]

@chipsworld
If you put this cyclecntfrm.upcttxt.SetFocus in the beginning of the Sub, say before lkup = upcnum, does it work?

Akuini,
Nope! Exact same result.

I still do not understand why after hitting DEBUG, and then the proceed arrow on the ribbon, it works perfectly.

 

[Akuini]

Akuini,
Nope! Exact same result.

After you put this cyclecntfrm.upcttxt.SetFocus in the beginning of the Sub, where did the code stop? In cyclecntfrm.upcttxt.SetFocus?

 

[chipsworld]

After you put this cyclecntfrm.upcttxt.SetFocus in the beginning of the Sub, where did the code stop? In cyclecntfrm.upcttxt.SetFocus?

Akuini,
Yes. Failed at the cyclecntfrm.upctxt.setfocus line. Does no matter where I put it in the sub.

 

[Akuini]

Yes. Failed at the cyclecntfrm.upctxt.setfocus line. Does no matter where I put it in the sub.

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.