reasonancefa
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
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