Excel User Form with Unique Numbers

Microsoft Office excel 2003 programming...
December 24, 2009 at 00:26:12
Specs: Windows XP
I am having a really big problem. How to make a Userform to have sequential numbering from ALGKW0001, ALGKW0099, ALGKW0999,

In details... the numbering should come from the right while taking out the zeros out when it is incrementing.

Also, after saving the UniqueID, the next Id should appear in the Form when opened again.

Kindly help a neophyte... such as myself


See More: Excel User Form with Unique Numbers

Report •


#1
December 24, 2009 at 05:23:35
Hi,

I don't know enough about your User Form and what it interacts with to give you a definitive answer, but if your ID was stored in a workbook, this code in the UserForm Initialization code would get the ID, increment to the next ID in the format you want and save the new ID.

The ID would be available to use elsewhere in your form if the DIM statement for the variable strID was in the General Declarations part of the User form code, (not in the initialization section).

In the initialization code you could for example copy the strID to the Caption of a Label on your user form.

Here is the code:

Option Explicit
Dim strID As String


Private Sub UserForm_Initialize()

Dim strStem As String
Dim strIncr As String
Dim intIncr As Integer

strStem = "ALGKW"
'get current ID
strID = Workbooks("Book1").Worksheets("Sheet1").Range("A1").Text
'get the part after the stem
strIncr = Right(strID, Len(strID) - Len(strStem))
'convert the 'number' part of the ID to a real number
intIncr = CInt(strIncr)
'increment the number
intIncr = intIncr + 1
'convert it back to text with leading zeros
strIncr = Format(intIncr, "0000")
'add the new 'number' to the stem
strID = strStem & strIncr
'save the new ID
Workbooks("Book1").Worksheets("Sheet1").Range("A1").Value = strID
'copy the ID to Label 3 on this User form
Me.Label3.Caption = strID
End Sub

You will have to change the address for where the ID is stored. Other options are to create and use a small text file to store the ID, or use a Registry entry to store it (Excel vba provides access to a registry key that users can save to and read from).

Regards


Report •

#2
December 27, 2009 at 00:52:33
Hi!

I have tried the "Test" for the code, yet its giving an 'Error 5 in "strID = Workbooks("Test").Worksheet("JobID").Range("B3").Text 'get the part after the stem" - I believe i had place the edits correctly on the code yet its calling for an argument.

To Check:

Option Explicit
Dim strID As String


Private Sub cmdCancel_Click()

Unload Me

End Sub
Private Sub cmdClear_Click()

Call frmTEST_Initialize

End Sub


Private Sub cmdAdd_Click()

Call frmTEST_Initialize

ActiveWorkbook.Sheets("JobID").Activate
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = Me.txtDate.Value
ActiveCell.Offset(0, 1) = Me.Label3.Caption
Range("A3").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select

End Sub

Private Sub frmTEST_Initialize()

Me.txtDate.Value = Now()
Me.txtDate = Format(Date, "dd mmm yyyy hh:mm")

Dim strStem As String
Dim strIncr As String
Dim intIncr As Integer

strStem = "ALGKW"
'get current ID

strIncr = Right(strID, Len(strID) - Len(strStem))
'convert the 'number' part of the ID to a real number
intIncr = CInt(strIncr)
'increment the number
intIncr = intIncr + 1
'convert it back to text with leading zeros
strIncr = Format(intIncr, "0000")
'add the new 'number' to the stem
strID = strStem & strIncr
'save the new ID
Workbooks("Test").Worksheets("JobID").Range("B3").Value = strID
'copy the ID to Label 3 on this User form
Me.Label3.Caption = strID


End Sub

Private Sub UserForm_Click()

End Sub


Thank you so much in advance. If I get this correctly, I can finish up the other interactions. But, What if it was "Dim As Long" with the date. does it mean that the uniqueID and the txtdate would be understood as 1 reference?



Report •

#3
December 27, 2009 at 08:20:32
Hi,

Looking at the code you posted, am I right in assuming that you are calling the Form from two buttons:
cmdClear and cmdAdd

Are these command buttons embedded in the worksheet JobID ?

If this is the case, the code:

Option Explicit
Dim strID As String

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClear_Click()
Call frmTEST_Initialize
End Sub

Private Sub cmdAdd_Click()
Call frmTEST_Initialize
...
is all in the Worksheet("JobID") object

The code for the form is in a User Form module in the Workbook "Test"

As a result, the strID variable has been DIM'd in the JobID worksheet and is not available to the User Form module containing frmTEST.

There are two ways to deal with this:
1. Assign the value of strID inside the form initialization code.
If the ID is in a cell on worksheet JobID in say cell A1 then strID=Worksheets("JobID").Range("A1").Text should work

2. Make strID a global variable.
This will give you more flexibility where to assign and use strID, but the code that assigns strID will have to be in a standard module (e.g., Module1). Excel's global variables are not available to the code for command buttons embeded in worksheets, but you can pass control from the click event of an embedded command button to a subroutine in a standard module and then assign strID. strID will then be available in all subroutines in the form, including the Initialize routine.

Regarding your last question about "Dim As Long" with the date, I wasn't entirely clear what you were asking.
If you Dim a variable as long (say dim lngVal as Long), and a cell, say D1 contains a date or date/time, then lngVal = Range("D1").Value wil return a number. If you had strID = Range("D1").Text it would return the date/time as a string, i.e. as it appears in the cell. (Excel stores dates/times as a number, starting with 1 for 1 January 1900 and with time as the deimal part of the number. 40174.5 is 27 December 2009 at 12 noon, i.e. 0.5 of the way through the day). Cells have both Value and Text properties.

Regards


Report •

Related Solutions

#4
December 30, 2009 at 00:50:52
Hi!

Thank you for all your help, its working wonderfully great!

Cheers and a 2010 Happy New Year to you and Computing.net


Report •

#5
December 30, 2009 at 05:28:05
Hi,

Glad to hear its working.

Happy New Year to you

Humar


Report •


Ask Question