Thread: Generate Serial No. by using VBA

So how will you serial number be made up?

Hello Anwitha,

I don't know much about VBA, but try the attached. The code basically doing is,

1. Unprotect the sheet
2. Adding serial numbers in Col_A
3. Protect the rows 1 to Row contain the last SL Number.

Hope this helps.

Private Sub CommandButton1_Click()
    ActiveSheet.Unprotect Password:="password"
  
    Dim FR As Long, LR As Long
    FR = Range("A1").End(xlDown).Row + 1
    LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    
    With Range("A" & FR & ":A" & LR)
        .FormulaR1C1 = "=MAX(R" & FR - 1 & "C:R[-1]C)+1"
        .Value = .Value
    End With
    On Error Resume Next
    
    ActiveSheet.Protection.AllowEditRanges(1).Delete
    ActiveSheet.Protection.AllowEditRanges.Add Title:="Range1", Range:=Rows(LR + 1 & ":" & Rows.Count)
    ActiveSheet.Protect Password:="password"

End Sub

Why use VBA? A Dynamic Named Range and the Max Function would work.

If you need VBA the use the worksheet_change event

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target > "" Then Exit Sub
    Dim rRng As Range
    Set rRng = Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp))
    If Intersect(Target, rRng) Is Nothing Then Target.Value = _
       Application.WorksheetFunction.Max(rRng) + 1
End Sub

Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste