Fill text box in userform

[adam]

Im having the following code to put up a staff ID in my userform

Code: Select all

  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.

Any help would be appreciated

[HansV]

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:

Code: Select all

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.

[adam]

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.

[HansV]

I'd put the letter S in cell A10, and the number 0001 in cell B10.

[HansV]

Alternatively, set the number format of cell A10 to the custom format "S"0000.
If the actual value of cell A10 is the number 3, it will display S003.

[adam]

Alternatively, set the number format of cell A10 to the custom format "S"0000.
If the actual value of cell A10 is the number 3, it will display S003.

I have tried this before you. It worked fine with the worksheet.But does not show in the userform.

I'd put the letter S in cell A10, and the number 0001 in cell B10.

I've tried the above but it does not seem to work with the following code

Code: Select all

Private Sub UserForm_Initialize()
  With Worksheets("StaffList").Range("A10")
    .Value = .Value + 1
    Me.txtStaffID = Format(.Value, "0000")
  End With
End Sub

[adam]

Never mind I got it fixed for myself.

[HansV]

Change the line

Me.txtStaffID = Format(.Value, "0000")

to

Me.txtStaffID = Format(.Value, "\S0000")

[adam]

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.

Code: Select all

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

thanks in advance

[HansV]

Does it make a difference if you change the line

IDArray = Range("StaffID")

to

IDArray = ws.Range("StaffID")

[adam]

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

Code: Select all

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

[HansV]

You don't set the value of IDArray at all in this version of the code.

[adam]

well I did use the following lines
Set ws = Worksheets("StaffList")
IDArray = ws.Range("StaffID")
But still I get error

[HansV]

Could you attach a stripped down and - if necessary - zipped copy of the workbook?

[adam]

Attached please find the document of interest.

[HansV]

There is no range named "StaffID". This causes the code to fail.

[adam]

So what do I have to do to prevent this?

[HansV]

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.

[adam]

I would be happy if you could specify the answer with an example workbook.

[HansV]

I can't create an example since I don't know what you're trying to do and what StaffID is meant to be.