Fill text box in userform

[adam]

Dear Hans,

Following is what I want in detail;
Macro Button
When the form is loaded after clicking the macro button, I want only the text in the text box txt staffID to be visible.

Ok Button
When I click the OK button after filling all the fields with staff data, I want all the data except the data in the textbox txtstaffID from the form to get cleared, but copied into the sheet stafflist; moreover the last number in the txtbox txtstaffID get changed to the next available number.

Say for example; I fill all the fields in the form with the StaffID in the textbox txtstaffID with S0001 and press OK I want the number in the textbox txtStaffID to get changed to S0002 with all the fields getting copied into the shett "StaffList" & next available Staffid to be visible in the form.

Clear Button
When I click the clear button in the form I want all the data in the form except the text in the field txtSftaffID to get remained.

Exit Button
When I press the Exit button the form gets out of sight. Lets say after filling only two fields of the form and I suddenly realized that the staff already exits, so I want to cancel the registration by pressing the Exit button. The form gets hidden and when I load it again I want only the StaffId to be visible.

I hope this makes my question clear.

[HansV]

On the sheet StaffList, give the range from A10 down until the end the name StaffID.
Set the number format of this range to the custom format \S0000

In the code behind the OK button, write only the numeric part of txtStaffID to the sheet:

ws.Cells(iRow, 1).Value = Mid(Me.txtStaffID.Value, 2)

and don't clear the text box, but increase its value instead:

Me.txtStaffID.Value = Format(ws.Cells(iRow, 1).Value + 1, "\S0000")

Don't set focus to txtStaffID (it is disabled), but to txtNationalID instead:

Me.txtNationalID.SetFocus

[Rudi]

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

Have a look at this example. You will need to change the range name that I hi-lited in yellow and point it to where you need it. As Hans said...we do not know where you want to STAFFID as a range name.


EDIT NOTE:
Sorry....please scrap this post. I was replying to the last post on page 1. I did not notice that there was a page two on the thread.

[adam]

If I may ask, I would be happy if you could help me by placing your lines in the following code. I'm a little confused.

Code: Select all

Option Explicit
Private Sub cmdExit_Click()
Me.Hide
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()
  With Worksheets("StaffList").Range("A10")
  .Value = .Value + 1
  Me.txtStaffID = Format(.Value, "\S0000")
End With
End Sub

[HansV]

The lines that I posted go into Private Sub cmdOK_Click(), and I indicated what the lines I posted do. By reading through cmdOK_Click and comparing the lines, you'll see which lines to replace.

[adam]

I'm sorry but with the following modification it aint still working

Code: Select all

Option Explicit
Private Sub cmdExit_Click()
Me.Hide
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 National ID
If Trim(Me.txtNationalID.Value) = "" Then
  Me.txtNationalID.SetFocus
  MsgBox "Please enter National 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 = Mid(Me.txtStaffID.Value, 2)
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 = Format(ws.Cells(iRow, 1).Value + 1, "\S0000")
Me.txtNationalID.Value = ""
Me.txtName.Value = ""
Me.txtDesignation.Value = ""
Me.txtJoinDate.Value = ""
Me.txtContactNo.Value = ""
Me.txtAddress = ""
Me.txtNotes = ""
Me.txtNationalID.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()
  With Worksheets("StaffList").Range("A10")
  .Value = .Value + 1
  Me.txtStaffID = Format(.Value, "\S0000")
End With
End Sub

[HansV]

In what sense isn't it working?

[adam]

after writing the fields and when I press the clear button the textbox txtStaffId gets cleared with the rest. and when I fill half of the text boxes and when I open the form again the fields that I filled remain the same instead of being cleared.

[HansV]

Could you post the workbook with the latest code?

[adam]

Attached please find the the workbook of interest

[HansV]

You still haven't created a range named StaffID, as suggested several times, with detailed instructions, higher up in this thread.
Neither have you formatted the range for the StaffIDs.

The UserForm_Initialize code of your userform now only looks at cell A10. That way, you won't be able to fill rows below row 10.

[adam]

Hans, Now I have pointed out the misunderstanding between you & me. What I kept asking several times is the following part of the code

Code: Select all

  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

your modification from the beginning also works the way I wanted. What I was asking was that when I click the clear button the textbox StaffID gets cleared with the rest of the textboxes. I don't want that to happen.

I would be pleased if you could help me to add a modification to the above code to prevent that clearing of the textbox txtStaffID

[HansV]

You could modify it like this:

Code: Select all

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") And ctl.Name <> "txtStaffID" Then
      ctl.Value = ""
    End If
  Next ctl
End Sub

[Rudi]

Syntax Hans...

Code: Select all

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") And ctl.Name <> "txtStaffID" Then
      ctl.Value = ""
    End If
  Next ctl
End Sub

[HansV]

Thanks, I just typed it in the reply.

[adam]

Like Rudi said that modification is giving me a syntax or compile error, Hans

[Rudi]

Like Rudi said that modification is giving me a syntax or compile error, Hans

You can copy the code from the latest post Adam...both I and Hans fixed the syntax error. Hans forgot the closing quotation in this line: And ctl.Name <> "txtStaffID"

It is fixed now...so recopy the code and paste it into your VBA.

[adam]

Thanks guys. That's like a piece of dream come true. One more thing to end up the thread. The code which does the StaffID thing is as follows

Code: Select all

    Private Sub UserForm_Active()
    Dim ws As Worksheet
    Dim IDArray As Variant
    Set ws = Worksheets("StaffList")
    IDArray = ws.Range("StaffID")
    Me.txtStaffID.Enabled = False
    Me.txtStaffID.Value = Format(WorksheetFunction.Max(IDArray) + 1, "\S0000")
    End Sub

When I close the workbook and open it and load the form the StaffID is not visible in the form with the current available number. I did place the above code under he initialize code so that the number stays visible when I load the form could you pleas let me know whether it is possible to place it under Private Sub UserForm_Initialise() if so I can I modify the code

[HansV]

UserForm_Active is not a valid event, it should be UserForm_Activate. But I think UserForm_Initialize would be the proper place for the code.

[adam]

Yeah its a mistake that I had written, Private Sub UserForm_Active
I tried by placing the initialize sub as follows but the form does not seem to open what have I done wrong here

Code: Select all

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim IDArray As Variant
    Set ws = Worksheets("StaffList")
    IDArray = ws.Range("StaffID")
    Me.txtStaffID.Enabled = False
    Me.txtStaffID.Value = Format(WorksheetFunction.Max(IDArray) + 1, "\S0000")
    End Sub