PDA

View Full Version : Staff ID Number format



fiza
04-01-2010, 11:23 AM
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
04-01-2010, 12:12 PM
With Me.TextBox1

.Text = Application.Text(.Text, "\S0000")
End With

fiza
04-01-2010, 12:25 PM
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
04-01-2010, 12:41 PM
Not as elegant as something Bob would provide but maybe something like this:

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

fiza
04-01-2010, 12:46 PM
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
04-01-2010, 12:55 PM
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
04-01-2010, 01:00 PM
Private Sub UserForm_Initialize()
With Worksheets("CustomerList").Range("F7")
' .Value = .Value + 1
Me.txtStaffID = Format(.Value + 1, "\S0000")
End With
End Sub

fiza
04-02-2010, 04:56 AM
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
04-02-2010, 10:18 AM
Add a CustomDocumentProperty to your workbook and increment it as required


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

fiza
04-02-2010, 12:41 PM
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
04-02-2010, 03:35 PM
Did you add a Property to the Workbook properties?

Bob Phillips
04-03-2010, 01:49 AM
Another approach.

First declare MyID as a public variable in a standard module



'-------------------------------------------------------------
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


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
04-03-2010, 05:00 AM
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
Private Sub UserForm_Initialize()
GetID
End Sub

fiza
04-04-2010, 10:24 AM
thanks for the help. I'm happy now