VBA code issue to be corrected for selected row to be displayed in multiple textboxes in user form

[raghavanhr]

Dear Adviser,

I recently developed a user form to update data into my excel sheet with reference to the employee number. I have listed the existing data in the Listbox and developed the respective textbox for each column heading, while clicking the spin button it starts working only after the second click, I need that to be on the first click and the same to work when I search for any particular employee number the spin should work further to next number or previous number by clicking the spin button with ref. to the employee number from the search text box.

Second, the macro displays the data from other open workbooks, so I have to close all other open workbooks to work with the user form. kindly help me how to avoid that in the same.

Third, when I click update the text box value would be null after updating.

Finally, while referring to the data to Listbox from excel I have given the reference as approximate 65000 rows, instead what I need is that have to refer the actual number of rows from the user database in excel to Listbox.

Option Explicit
Public iWidth As Integer
Public iHeight As Integer
Public iLeft As Integer
Public iTop As Integer
Public bState As Boolean
Private Sub cmdClose_Click()
Dim iClose As VbMsgBoxResult
iClose = MsgBox("Confirm if you want to Close", vbQuestion + vbYesNo, "Leaver details update")
If iClose = vbYes Then
Workbooks("EOSB Data Form.xlsm").Save
Workbooks("EOSB Data Form.xlsm").Close
Unload Me
End If
End Sub
Private Sub cmdFullScreen_Click()
Call Maximize_Restore
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        Me.cmdClose.SetFocus
    End If
End Sub
Private Sub cmdSearch_Click()
Dim EmpCode As String
Dim i As Integer
Dim lastrow As Integer
lastrow = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
EmpCode = Trim(txtEmpCode.Text)
For i = 2 To lastrow
If Worksheets("Data").Cells(i, 1).Value = "" Then
 Exit For
End If
    If Worksheets("Data").Cells(i, 1).Value = EmpCode Then
        lblName = Worksheets("Data").Cells(i, 2).Value
        txtNoticeAbsence.Text = Worksheets("Data").Cells(i, 3).Value
        txtNOT.Text = Worksheets("Data").Cells(i, 4).Value
        txtSOT.Text = Worksheets("Data").Cells(i, 5).Value
        txtLetterDate.Text = Worksheets("Data").Cells(i, 6).Value
        txtLWD.Text = Worksheets("Data").Cells(i, 7).Value
        txtLSD.Text = Worksheets("Data").Cells(i, 8).Value
        txtRamadanAL.Text = Worksheets("Data").Cells(i, 9).Value
        txtUAA.Text = Worksheets("Data").Cells(i, 10).Value
        txtLastSalary.Text = Worksheets("Data").Cells(i, 11).Value
        txtStatus.Text = Worksheets("Data").Cells(i, 12).Value
    End If
Next

End Sub
Private Sub cmdShowExcel_Click()
Application.Visible = True
UserForm.Hide
End Sub
Private Sub cmdUpdate_Click()
Dim EmpCode As String
Dim i As Integer
Dim lastrow As Integer
EmpCode = Trim(txtEmpCode.Text)
lastrow = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
For i = 2 To lastrow
    If Worksheets("Data").Cells(i, 1).Value = EmpCode Then
        Worksheets("Data").Cells(i, 2).Value = lblName
        Worksheets("Data").Cells(i, 3).Value = txtNoticeAbsence.Text
        Worksheets("Data").Cells(i, 4).Value = txtNOT.Text
        Worksheets("Data").Cells(i, 5).Value = txtSOT.Text
        Worksheets("Data").Cells(i, 6).Value = txtLetterDate.Text
        Worksheets("Data").Cells(i, 7).Value = txtLWD.Text
        Worksheets("Data").Cells(i, 8).Value = txtLSD.Text
        Worksheets("Data").Cells(i, 9).Value = txtRamadanAL.Text
        Worksheets("Data").Cells(i, 10).Value = txtUAA.Text
        Worksheets("Data").Cells(i, 11).Value = txtLastSalary.Text
        Worksheets("Data").Cells(i, 12).Value = txtStatus.Text
    End If
Next

End Sub
Private Sub SpinButton_Change()
Dim c As Integer
    
    txtSpinEmpCode.Value = SpinButton.Value
    
    CurrentLengthLabel.Width = (MaxLengthLabel.Width / SpinButton.Max) * SpinButton.Value

    c = (255 / SpinButton.Max) * SpinButton.Value
    
    CurrentLengthLabel.BackColor = RGB(c, 255 - c, 0)
    
    
        Dim C1, C2
        If SpinButton.Value > 1 Then
        C2 = SpinButton.Value
        C1 = "A" & C2
        txtEmpCode.ControlSource = C1

        C1 = "B" & C2
        lblName.ControlSource = C1

        C1 = "C" & C2
        txtNoticeAbsence.ControlSource = C1

        C1 = "D" & C2
        txtNOT.ControlSource = C1
        C1 = "E" & C2
        txtSOT.ControlSource = C1

        C2 = SpinButton.Value
        C1 = "F" & C2
        txtLetterDate.ControlSource = C1

        C1 = "G" & C2
        txtLWD.ControlSource = C1

        C1 = "H" & C2
        txtLSD.ControlSource = C1
        C1 = "I" & C2
        txtRamadanAL.ControlSource = C1

        C2 = SpinButton.Value
        C1 = "J" & C2
        txtUAA.ControlSource = C1

        C1 = "K" & C2
        txtLastSalary.ControlSource = C1

        C1 = "L" & C2
        txtStatus.ControlSource = C1
End If
End Sub
Private Sub UserForm_Activate()

Application.Visible = False
lstBoxData.ColumnCount = 24
lstBoxData.RowSource = "A2:L6000"
UserForm.lstBoxData.ColumnWidths = "50,150,95,65,65,65,65,65,75,65,65,65"
lblDate.Caption = Format(Date, "dddd dd-mmm-yyyy")
txtSpinEmpCode.Visible = False
End Sub
Sub Maximize_Restore()
If Not bState = True Then
    iWidth = UserForm.Width
    iHeight = UserForm.Height
    iTop = UserForm.Top
    iLeft = UserForm.Left
    
    With Application
        .WindowState = xlMaximized
        UserForm.Zoom = Int(.Width / UserForm.Width * 100)
        UserForm.StartUpPosition = 0
        UserForm.Left = .Left
        UserForm.Top = .Top
        UserForm.Width = .Width
        UserForm.Height = .Height
    End With
    
        UserForm.cmdFullScreen.Caption = "Restore"
        bState = True
    Else
        With Application
            .WindowState = xlNormal
            UserForm.Zoom = 100
            UserForm.StartUpPosition = 0
            UserForm.Left = iLeft
            UserForm.Width = iWidth
            UserForm.Height = iHeight
            UserForm.Top = iTop
            
        End With

        UserForm.cmdFullScreen.Caption = "Full Screen"
        bState = False
End If
End Sub