View Full Version : Staff ID Number format
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
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
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.
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
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
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
thanks for the help. I'm happy now
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.