autonumbering in a userform
 

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

autonumbering in a userform
 

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" wrote in message
...
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

autonumbering in a userform
 

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