Staff ID Number format

[fiza]

Hi Anyone,
I have a textbox in my userform with the name txtStaffID. what I want is when the form is loaded the textbox txtStaffID to be visible with the Staff ID number.

Say if I write the Staff ID as 1 in the textbox txtStaffID, I want the number to be S0001.

When I click the Ok button in my form I want the number to automatically change to S0002 and so on.

Any help would be greatly appriciated.

Thanks in advance.

Cheers
Fiza

[Bob Phillips]

[vba]

With Me.TextBox1

.Text = Application.Text(.Text, "\S0000")
End With
[/vba]

[fiza]

your code does not seem to work the way as I wanted could you modify the following code so that the number appears as S0001

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

[lucas]

Not as elegant as something Bob would provide but maybe something like this:

[VBA]Private Sub UserForm_Initialize()
Dim snum As Long
snum = Worksheets("CustomerList").Range("F7").Value + 1
Me.txtStaffID = "S000" & snum
Worksheets("CustomerList").Range("F7").Value = Worksheets("CustomerList").Range("F7").Value + 1
End Sub
[/VBA]

[fiza]

I got it fixed now. I did replace the "0000" with "\S0000"
this is how the code is now

Private Sub UserForm_Initialize()
  With Worksheets("CustomerList").Range("F7")
    .Value = .Value + 1
    Me.txtStaffID = Format(.Value, "\S0000")
  End With
End Sub

Anyways, Thanks for the help.
I do appreciate that.

[fiza]

The number in the staffID textbox shows a new number each time I load the form and also the number in F7 of the worksheet gets changed along with that. is there a way to modify the code so that the number in the userform gets changed but not in the worksheet "F7".

[lucas]

[VBA]Private Sub UserForm_Initialize()
With Worksheets("CustomerList").Range("F7")
' .Value = .Value + 1
Me.txtStaffID = Format(.Value + 1, "\S0000")
End With
End Sub[/VBA]

[fiza]

I'm sorry with this modification neither the number in the userform nor the referenced number in the worksheet gets changed.

What im trying to get is each time I load the form the ID number in the form to be changed to the next available number but not the number in the worksheet.

[mdmackillop]

Add a CustomDocumentProperty to your workbook and increment it as required

[vba]
Private Sub UserForm_Initialize()
ActiveWorkbook.CustomDocumentProperties(1) = _
ActiveWorkbook.CustomDocumentProperties(1) + 1
TextBox1 = ActiveWorkbook.CustomDocumentProperties(1)
End Sub

[/vba]

[fiza]

Can you please specify your code in detail. I tried with the following modification. But it does not seem to work

    ActiveWorkbook.CustomDocumentProperties(1) = _
    ActiveWorkbook.CustomDocumentProperties(1) + 1
    txtSatffID = ActiveWorkbook.CustomDocumentProperties(1)

[mdmackillop]

Did you add a Property to the Workbook properties?

[Bob Phillips]

Another approach.

First declare MyID as a public variable in a standard module

[vba]

'-------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------
GetId
End Sub


'-------------------------------------------------------------
Private Sub GetId()
'-------------------------------------------------------------
Dim myID As Long

myID = 1 ' in case it doesn't already exist
On Error Resume Next
myID = Evaluate(ThisWorkbook.Names("UniqueId").RefersTo) + 1
ThisWorkbook.Names.Add Name:="UniqueId", RefersTo:="=" & myID
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[mdmackillop]

What im trying to get is each time I load the form the ID number in the form to be changed to the next available number but not the number in the worksheet.

or for the form opening to be counted
[VBA]Private Sub UserForm_Initialize()
GetID
End Sub
[/VBA]

[fiza]

thanks for the help. I'm happy now