.setfocus on userform not working

[janderson055]

I have a userform that allows me to add a new employee to a sheet, which adds some info to the sheet itself and creates a new sheet with that employees name on it. After all this process I try to setfocus back to the userform on the first text box, just by using myform.txtbox.setfocus yet it doesn't seem to work. Any help would be great.

Sub saveAddEmployee()
    Application.ScreenUpdating = False
    
    Dim rowCounter As Integer
    Dim curSheet As String
    
    curSheet = ActiveSheet.name
    
    rowCounter = 1
    
    Do Until Sheets("Employees").Cells(rowCounter, 1).Value = Empty
        rowCounter = rowCounter + 1
    Loop
    
    Sheets("Employees").Cells(rowCounter, 1).Value = frmAddEmployee.txtFirstName.Value & " " & frmAddEmployee.txtLastName.Value
    Sheets("Employees").Cells(rowCounter, 2).Value = frmAddEmployee.txtAddress.Value
    Sheets("Employees").Cells(rowCounter, 3).Value = frmAddEmployee.txtCity.Value
    Sheets("Employees").Cells(rowCounter, 4).Value = frmAddEmployee.cmboxState.Value
    Sheets("Employees").Cells(rowCounter, 5).Value = frmAddEmployee.txtZipCode.Value
    Sheets("Employees").Cells(rowCounter, 6).Value = frmAddEmployee.txtHourRate.Value
    
    Sheets.add After:=Sheets(Sheets.Count)
    ActiveSheet.name = frmAddEmployee.txtFirstName.Value & " " & frmAddEmployee.txtLastName.Value
    
    Range("a1").Value = "Pay Date"
    Range("b1").Value = "Pay Hours"
    Range("c1").Value = "Pay Amount"
    Range("d1").Value = "Pay Period"
    Range("A1:D1").Select
    Selection.Font.bold = True
    Cells.EntireColumn.AutoFit
    
    Range("B2:B2000").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
    End With
    Selection.NumberFormat = "0.00"
    
    Range("a1").Select
    
    clearFields 'sub that clears all the data in the userform
    
    frmAddEmployee.txtFirstName.SetFocus
    
    Sheets(curSheet).Select
    
    MsgBox "Employee Added Successfully"
    
    Application.ScreenUpdating = True
    
    frmAddEmployee.txtFirstName.SetFocus
    
End Sub

 

[CharlesH]

Hi,

When you created the userform you can set the "Tab" sequence.
If the .txtFirstname is not the first selection in the sequence then change the sequence
to the "Firstname" doing so I believe you would not need to set the Focus.
I could be wrong, but check it.

 

[janderson055]

I tried that, still didn't work, my tab index is set at 1. The userform is modeless as well, not sure if that makes a difference but if I remember right it does. Thanks.

 

[CharlesH]

Hi,

I modified your code.
I remove the call to clear the form.
Added a With statement to clear and set the focus.

Sub saveAddEmployee()
    Application.ScreenUpdating = False
    
    Dim rowCounter As Integer
    Dim curSheet As String
    Dim Ews As Worksheet
    Set Ews = Sheets("Employees")
    curSheet = ActiveSheet.Name
    
    rowCounter = 1
    
    Do Until Sheets("Employees").Cells(rowCounter, 1).Value = Empty
        rowCounter = rowCounter + 1
    Loop
    With Ews
        Cells(rowCounter, 1).Value = frmAddEmployee.txtFirstName.Value & " " & frmAddEmployee.txtLastName.Value
        Cells(rowCounter, 2).Value = frmAddEmployee.txtAddress.Value
        Cells(rowCounter, 3).Value = frmAddEmployee.txtCity.Value
        Cells(rowCounter, 4).Value = frmAddEmployee.cmboxState.Value
        Cells(rowCounter, 5).Value = frmAddEmployee.txtZipCode.Value
        Cells(rowCounter, 6).Value = frmAddEmployee.txtHourRate.Value
    
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = frmAddEmployee.txtFirstName.Value & " " & frmAddEmployee.txtLastName.Value
    
        Range("a1").Value = "Pay Date"
        Range("b1").Value = "Pay Hours"
        Range("c1").Value = "Pay Amount"
        Range("d1").Value = "Pay Period"
        Range("A1:D1").Select
        Selection.Font.Bold = True
        Cells.EntireColumn.AutoFit
    
        Range("B2:B2000").Select
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
        End With
        Selection.NumberFormat = "0.00"
    
        Range("a1").Select
    
        'clearFields 'sub that clears all the data in the userform
        With frmAddEmployee
            .txtLastName.Text = ""
            .txtAddress.Value = ""
            .txtCity.Value = ""
            .cmboxState.Value = ""
            .txtZipCode.Value = ""
            .txtHourRate.Value = ""
            With .txtFirstName
                .SetFocus
            End With
        End With
    
        Sheets(curSheet).Activate
    
        MsgBox "Employee Added Successfully"
    
        Application.ScreenUpdating = True

    End With
End Sub

 

[CharlesH]

Hi,

Cross posted..
.setfocus on userform not working