Hi

Im new to VBA. I would like Userform to auto generate a unique number and every time the form is open, it automatically increases its number before even updating records or click any buttons. (example, CRUINT000001...CRUINT000002...)

This Auto generated number will then be auto added to coloumn A named "Ref" in the worksheet. Im not sure what tool to use, label, textbox or commandbutton. Please help

Below are the codes I've created so far.

Thanks
Lizah

Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("INTERNAL")

'''find  first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
''  .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a recipient
If Trim(Me.txtRecipient.Value) = "" Then
  Me.txtRecipient.SetFocus
  MsgBox "Please enter a Recipient"
  Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPersonnel.Value
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Cells(iRow, 3).Value = Me.txtRecipient.Value
ws.Cells(iRow, 4).Value = Me.txtDescription.Value
ws.Cells(iRow, 5).Value = Me.CboType.Value
ws.Cells(iRow, 6).Value = Me.txtConsignment.Value
ws.Cells(iRow, 7).Value = Me.txtStatus.Value


'clear the data
Me.txtPersonnel.Value = ""
Me.txtDate.Value = ""
Me.txtRecipient.Value = ""
Me.txtDescription.Value = ""
Me.CboType.Value = ""
Me.txtConsignment.Value = ""
Me.txtStatus.Value = ""
Me.txtRecipient.SetFocus

End Sub

Private Sub CmdClose_Click()
 Unload Me
End Sub

Private Sub UserForm_Initialize()
 'Load the combobox with a variety of type of mails
    With Me.CboType
         .AddItem "Normal - Local"
         .AddItem "Normal - Registered"
         .AddItem "Courier - Normal"
         .AddItem "Courier - Quick"
         .AddItem "Courier - Dash"
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the button!"
  End If
End Sub