Generating Automatic Serial Numbers

[karmensoo]

I'm using MS excel template to generate an internal memo form. To help me keep track of my memo forms easier, I want excel template to generate automatic serial number everytime I fill-in a new memo form. For eg.

Memo to John - Excel to auto generate "Memo Serial # 001"
Memo to Karen - ...."Memo Serial # 002"
Memo to Wayne - ...."Memo Serial # 003"
etc...etc..


TQ

 

[Misca]

Here's a macro that should do what you're after:

Sub MemoSerial()

Dim Serial As Long
Dim Memo As String
Dim NM As Name

On Error Resume Next

'Uses the name "Serial" to house the serial values
Set NM = ThisWorkbook.Names("Serial")

If Not NM Is Nothing Then
'Name exists:
    Serial = Val(Mid(NM, 2, Len(NM))) + 1
Else
    Serial = 1
End If

'Changes the name "Serial" to match the new Serial:
ThisWorkbook.Names.Add Name:="Serial", RefersTo:="=" & Serial

If Serial < 100 Then
    Memo = Right("00" & Serial, 3)
Else
    Memo = Serial
End If

Memo = "Memo Serial # " & Memo

MsgBox Memo

End Sub

 

[karmensoo]

Misca, thanks 4d reply n code. Ermm..problem is I'm a newbie and do not know how to go about putting those codes in place

If you dont mind, can you guide me please

Tkns in advance.

 

[Misca]

What is your MemoTemplate like now? What does it do / how do you use it (= open the template document from an icon? Have the template workbook open and press a button that generates a new document?)?

And what do you want to do with the MemoSerials? My guess is you want to store the memos in a workbook and use it as a database but if my guess is correct, what it is exactly that you want to store? And how do you want to store it?

If you want to get a fully functional macro you need to give all the possible info.