Add one every save to a number

[noelmus]

Hi,
I have this number 000153/19 which indicates Chit No/ Year. I need that every time I save, the Chit No increases by 1 and the year increases by 1 after 31st December.
So for example if I save today, it becomes 000154/19 and if the next save is on 1st January 2020 it becomes 000155/20.

Thanks in advance.

 

[Kenneth Hobson]

In ThisWorkbook object with a named cell of "ChitNo" in sheet named sheet1:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim c As Range, a
  Set c = Worksheets("Sheet1").Range("ChitNo")
  c.NumberFormat = "@"
  If InStr(c, "/") = 0 Then
    c = "000001/" & Format(Date, "yy")
    Else
    a = Split(c, "/")
    a(0) = Format(a(0) + 1, "00000#")
    If Format(a(1), "00") <> Format(Date, "yy") Then a(1) = Format(Date, "yy")
    c = Join(a, "/")
  End If
End Sub

 

[noelmus]

Hi,
Already has these codes in work book:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim ws As Worksheet


Set ws = Worksheets("CHIT")
If Sh.Name = ws.Name Then
ws.Range("C12") = Application.UserName
End If


Set ws = Nothing
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("CHIT").Range("J9,J12,C14,E28,E29").Interior.ColorIndex = 6
End Sub