With Worksheets("StaffList").Range("A10")
.Value = .Value + 1
Me.txtStaffID = Format(.Value, "0000")
End With
End Sub
I have the value assigned a number in stafflist A10 with the format as S0001
I want this number to be visible when I load my userform, so that it keeps on changing when I press the Ok button.
Your thread had become very long, and you're asking something that is not directly related to the original question, so I've moved your post to a new thread.
If you want the value to be loaded automatically when the userform is opened, put the code in the UserForm_Initialize event of the userform:
Private Sub UserForm_Initialize()
With Worksheets("StaffList").Range("A10")
.Value = .Value + 1
Me.txtStaffID = Format(.Value, "0000")
End With
End With
If that is not what you want, please try to explain your question more clearly.
Well Hans, I want the number to be S0001 your modification still gives me the number in 0001 format.
I hope the above sentence makes my question clear.
Private Sub UserForm_Initialize()
With Worksheets("StaffList").Range("A10")
.Value = .Value + 1
Me.txtStaffID = Format(.Value, "0000")
End With
End Sub
Im using the following code to change the staffID number each time I press the Ok command button in my userform or when I load the form. but im getting error messages. could you please specify what have been done wrong in here.
Private Sub UserForm_Activate()
Dim ws As Worksheet
Dim IDArray As Variant
Set ws = Worksheets("StaffList")
IDArray = Range("StaffID")
Me.txtStaffID.Enabled = False
Me.txtStaffID.Value = Format(WorksheetFunction.Max(IDArray) + 1, "\S0000")
End Sub
the code does not seem to work either with your suggestion. here is the code for your reference. I want the number in the textbox txtStaffID to change to the next available number when I press the ok button. I have modified the code but I do get error messegese referencing to Me.txtStaffID.SetFocus
Option Explicit
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdClear_Click()
' Clear the form
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
Private Sub cmdOK_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("StaffList")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Staff ID
If Trim(Me.txtStaffID.Value) = "" Then
Me.txtStaffID.SetFocus
MsgBox "Please enter Staff ID"
Exit Sub
End If
If Not IsDate(Me.txtJoinDate.Value) Then
MsgBox "Please Enter Date Joined.", vbExclamation, "Staff Registration"
Me.txtJoinDate.SetFocus
Exit Sub
End If
'copy the data to the StaffList
ws.Cells(iRow, 1).Value = Me.txtStaffID.Value
ws.Cells(iRow, 2).Value = Me.txtNationalID.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value
ws.Cells(iRow, 4).Value = Me.txtDesignation.Value
ws.Cells(iRow, 5).Value = Me.txtJoinDate.Value
ws.Cells(iRow, 6).Value = Me.txtAddress.Value
ws.Cells(iRow, 7).Value = Me.txtContactNo.Value
ws.Cells(iRow, 8).Value = Me.txtNotes.Value
'clear the data
Me.txtStaffID.Value = ""
Me.txtNationalID.Value = ""
Me.txtName.Value = ""
Me.txtDesignation.Value = ""
Me.txtJoinDate.Value = ""
Me.txtContactNo.Value = ""
Me.txtAddress = ""
Me.txtNotes = ""
Me.txtStaffID.SetFocus
End Sub
Private Sub txtContactNo_AfterUpdate()
If IsNumeric(txtContactNo.Value) Then
txtContactNo.Text = Format(txtContactNo.Text, "000-0000")
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Cancel button!"
End If
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim IDArray As Variant
Me.txtStaffID.Enabled = False
Me.txtStaffID.Value = Format(WorksheetFunction.Max(IDArray) + 1, "\S0000")
End Sub
If you want to use Range("StaffID"), you have to define a named range StaffID.
Alternatively, replace StaffID with the cell address of the range containing the StaffIDs. Your workbook doesn't provide a clue, so I can't tell you what the cell address is.