View Full Version : count up cell value by one and automatically be protected

10-15-2017, 02:28 PM
Good day

Lets see who is up to the challenge

I have a workbook that has to do the following for me automatically:
Count up the value of n certain cell by 1 every time I open the workbook
And protect the workbook after I save it

I am planning to work from one master template, fill it in and save as.
Keeping the template empty

The master template should change the value up by one of a certain cell but using the following VBAcode does not work because lets say the master template starts with the value 1 and lets call the saved as document slave.

Private Sub Workbook_Open()
Sheets(1).[G2] = Sheets(1).[G2] + 1
End Sub

If I open the master template it counts up to 2 and when I save as, the slave's value is now 2 but when I open the slave its value will want to change to 3 because the macro goes with it as well witch i do not want
and the master will open again as 2 because it was not saved under master and the master does not remember it changed to 2 already

Then using the following code to protect automatically when I save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Protect Password:="qwerty", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub

The problem with this is.
When I open the workbook again it can not automatically change the value of the certain cell up by 1 because it is now protected.
And I can not insert text box
Also I will have to enter the password now if I open it again to fill it in

If i protect workbook manually and check the edit objects box then i can insert text box

In a perfect world if this is possible the master template should count up the cell by one and remember its history, but it should not protect the workbook.
Then if I save as then the slave should lose this count up by one macro but automatically be protected (except for inserting a text box) by n password only I know

The reason
This workbook is not going to be used by me but only I can know the password
It is going to be use to create certificates and no certificate can have the same number and can not be edited after it is saved
The only edit is if there is a mistake on the certificate then this person can insert text box saying canceled in red

Thank you for the feedback in advance

10-15-2017, 02:32 PM
I do apologize in advance... I'm not to good with words so it might be confusing to read

10-15-2017, 03:33 PM
That's no challenge. One line solves it.

Get back to me when you have a real challenge,

10-15-2017, 03:34 PM
If Thiswokbook.Name <> "Master Template" Then Exit Sub

10-15-2017, 09:30 PM
Put a comment in G2 of the number 1.
Then put this code in the ThisWorkbook code module

Private Sub Workbook_Open()
On Error Resume Next
With Sheets(1).Range("G2")
.Value = Val(.Value) + Val(.Comment.Text)
End With
On Error GoTo 0
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then Sheets(1).Range("G2").Comment.Delete
End Sub