thank you, thank you, thank you, thank you
...oh by the way did I say Thanks??

Many thanks for ur help - works just great

"ND Pard" wrote:

Private Sub UserForm_Activate()
'This subprocedure will run every time the userform is activated

'activate the desired worksheet
Worksheets("MyWrkSht Name").Activate

'Select a cell that is in column A far, far below a possible last row
Range("A65000").Select

'Move the cursor up to the last row in column A
ActiveCell.Offset.End(xlUp).Select

'Move the cursor down 1 cell, ie, to the next blank row
ActiveCell.Offset(1).Select

'Format the cell to text
Selection.NumberFormat = "@"

'place todays date into the cell
ActiveCell.Value = Format(Now(), "mmddyy")

'if the date is equal to the date in the cell above
If Left(ActiveCell.Offset(-1), 6) = ActiveCell.Value Then

'add 1 to the end of the value in the 7 & 8 characters of the above
cell
ActiveCell.Value = ActiveCell.Value &
Format(Val(Mid(ActiveCell.Offset(-1), 7, 2)) + 1, "00")
Else

'else, add 01 to the end of today's date
ActiveCell.Value = ActiveCell.Value & "01"
End If

End Sub

"Anthony" wrote:

Hi all,

I have a userform that when loaded I would like it to show a unique
reference number and display it in next available cell in column A of 'data'
sheet
That number should be made up from todays date in format ddmmyy plus a
rolling number starting from 1.
for example if the user displays the userform for the first time today the
unique number would be 05080801, they open the form again and this time it
would be 05080802, and again 05080803.

However when they open the userform tomorrow for the first time the
reference number would be 06080801

any help greatly appreciated with this as I have no idea