Adding File version Excel VBA

Question

I want to add and change file version in cell P76 every time there is a change in file.

I tried this function but it is showing Excel version and it is not changing.

Function ExcelVersion()
    ExcelVersion = Application.Version
End Function

and in cell P76 (Sheet2)

=ExcelVersion()

I want it to show like VERSION 001 and every time (sheet2) update of change it change to next eg VERSION 001 to VERSION 002 and so on and in cell P77 (Sheet2) Dates and time of update.

I also try to add this

Private Sub submit_Click()     
    Dim i As Integer
    i = 1
    Cells(P76).value = "VERSION 00" & i + 1
End Sub

but not working

Application.Version will give the Excel version but not the change versions of your workbook,should try in some other way — Siva, Feb 04 '16 at 17:23
By the way what do you mean by every time, are expecting this to happen every times user modify data and save it, then you code should increase version number — Siva, Feb 04 '16 at 17:25
`Application.Version` is the Excel Version - as in 14.0 is Excel 2010, 11.0 is Excel 2003. If you want the update version you'll have to create a global variable and increment each time there's an update. — Darren Bartrup-Cook, Feb 04 '16 at 17:27
Sorry I can't give you code as I'm texting via mobile. You have predefined before close function.In that function you can write code — Siva, Feb 04 '16 at 18:13
I found this. [http://stackoverflow.com/questions/131605/best-way-to-do-version-control-for-ms-excel](http://stackoverflow.com/questions/131605/best-way-to-do-version-control-for-ms-excel) — Andre Turina, Feb 04 '16 at 18:23
@AndreTurina i read that earlier before posting question that is for saving file i just want counter in cell P76 which should update on change of Sheet — Muhammad Khurrum Butt, Feb 04 '16 at 18:38
I know its very simple code i don know how to apply eg i = integer P76 = i + 1 — Muhammad Khurrum Butt, Feb 04 '16 at 18:40

score 1 · Accepted Answer · answered Feb 04 '16 at 19:39

Place this in "ThisWorkbook". It will add the version number without the "Version" string. I would recommend adding "Version" in an adjacent cell in order to keep it simple.

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Sheets("Sheet2").Range("P76").Value = Sheets("Sheet2").Range("P76").Value + 1
    Sheets("Sheet2").Range("P77").Value = Now

End Sub

If you need "version" to be in the same cell as the number itself, comment and I will edit my response to reflect that functionality.

Also, this will not prevent users from editing the version number, time etc. just so you are aware.

it works well i added Version in adjacent cell with little alignment looks good — Muhammad Khurrum Butt, Feb 04 '16 at 20:34

Andy Galloway · Answer 2 · 2020-01-17T13:44:13.327

I think this is what you are looking for. It is placed in the workbook BeforeSave event. In cell P67 you would have the word "Version:" followed by the version number. This text before the version number has 9 characters, including the space.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim varVersion As Long
Sheets("Sheet2").Activate
varVersion = Right(Range("P67").Text, Len(Range("P67")) - 9)
Range("P67") = "Version: " & varVersion + 1
Range("P77").Value = Format(Now, "dd mmm yyyy")

End Sub

Adding File version Excel VBA

2 Answers2