autonumbering in a userform

[Mark Campbell]

I have created a userform for entering data into a list.
Each time the form is opened it automatically generates a reference number
and current date using the code listed below:

My problem is that if the userform is closed without entering data to the
list - the number on the form will still increase (next time opened) - also I
want the userform to be available to a number of users over a network so this
type of counter will not work very well.

Idealy I would like the userform on acitvation to search my list for the
largest number and + 1 to give the new number - is this possible what code
should i use?


Private Sub UserForm_initialize()
counter = GetSetting("XYZ Corp", "issuenum", "count", 0)
counter = counter + 1
SaveSetting "XYZ corp", "issuenum", "count", counter
Range("issuenum") = counter
Range("datelogged") = Now

If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

P.s - The date code I am using gives dates in the format m/d/y I would
prefer dates in the format d/m/year - how can I do this.

Thanks for any help

[Peter T]

Looks like you are writing the incremented counter to the registry in the
form's intitalise event. Why not do that when the form unloads subject to
checking the incremented value should be written. Put the code in the event
of whatever control closes the form or Queryclose.

Not sure where your list is but maybe something like

vList = Array(4, 2, 7, 4)
maxval = Application.WorksheetFunction.Max(vList)

Debug.Print Format(DateValue(ActiveCell.Value), "d/m/yyyy")

Regards,
Peter T

"Mark Campbell" <[email protected]> wrote in message
news:[email protected]...
> I have created a userform for entering data into a list.
> Each time the form is opened it automatically generates a reference number
> and current date using the code listed below:
>
> My problem is that if the userform is closed without entering data to the
> list - the number on the form will still increase (next time opened) -
also I
> want the userform to be available to a number of users over a network so
this
> type of counter will not work very well.
>
> Idealy I would like the userform on acitvation to search my list for the
> largest number and + 1 to give the new number - is this possible what code
> should i use?
>
>
> Private Sub UserForm_initialize()
> counter = GetSetting("XYZ Corp", "issuenum", "count", 0)
> counter = counter + 1
> SaveSetting "XYZ corp", "issuenum", "count", counter
> Range("issuenum") = counter
> Range("datelogged") = Now
>
> If IsDate(ActiveCell.Value) Then
> Calendar1.Value = DateValue(ActiveCell.Value)
> Else
> Calendar1.Value = Date
> End If
> End Sub
>
> P.s - The date code I am using gives dates in the format m/d/y I would
> prefer dates in the format d/m/year - how can I do this.
>
> Thanks for any help

[Mark Campbell]

Thks Peter - codes worked a treat

"Mark Campbell" wrote:

> I have created a userform for entering data into a list.
> Each time the form is opened it automatically generates a reference number
> and current date using the code listed below:
>
> My problem is that if the userform is closed without entering data to the
> list - the number on the form will still increase (next time opened) - also I
> want the userform to be available to a number of users over a network so this
> type of counter will not work very well.
>
> Idealy I would like the userform on acitvation to search my list for the
> largest number and + 1 to give the new number - is this possible what code
> should i use?
>
>
> Private Sub UserForm_initialize()
> counter = GetSetting("XYZ Corp", "issuenum", "count", 0)
> counter = counter + 1
> SaveSetting "XYZ corp", "issuenum", "count", counter
> Range("issuenum") = counter
> Range("datelogged") = Now
>
> If IsDate(ActiveCell.Value) Then
> Calendar1.Value = DateValue(ActiveCell.Value)
> Else
> Calendar1.Value = Date
> End If
> End Sub
>
> P.s - The date code I am using gives dates in the format m/d/y I would
> prefer dates in the format d/m/year - how can I do this.
>
> Thanks for any help

[ppminj]

Hi,

I have tested a user form for inputting a list of names. As of now I put the serial number myself. What I require now is that the serial number should be auto generated starting from 0001, 0002, 0003.....and so on.

I also have a clear button, which clears the wrong data just incase the user inputs a wrong entry. If I do clear then the auto serial number should not be cleared off. Just the wrong name should be cleared and made ready for fresh input with the auto generated serial number.

I am sure there must be a counter or something for the same.

I have seen a few codes also in the web, however not able to figure out it to work.

Request help.

I will highly grateful.

I am attaching the sample code for reference.

Thanks in advance.

Pawan