Macro to Activate on opening Workbook

[Hudco]

I want a macro to activate as a workbook is opened either as the original .xltm or as the .xlsm (when template activated).

The macro is the untick a checkbox on the sheet that opens.

Alternatively I want to be able to password protect the template but allow users to create a workbook from the template.

The reason for this question is that I have a template which I want users to actively "tick" the checkbox but some staff have opened the template, ticked the box and saved the template instead of opening and creating a new workbook from the template.

Thanks

 

[dougmarkham]

Hi Hudco,

With regards to making a macro start when a workbook is opened, here is code for that bit:

Private Sub Workbook_open()
Call Your_Macro_here 'Macro1

End Sub

You have to put this code in the ThisWorkbook object in VB Editor (accessible from the developer tab in excel).

Kind regards,

Doug.

 

[mole999]

but if the user has macros disabled, then the code won't work

 

[Hudco]

Thanks guys,
Firstly I don't have macros disabled but valid point
Secondly, I found that macro but had it in the wrong spot. Also I was using the following to untick the CheckBox1:

Private Sub Workbook_open()
CheckBox1 = False

End Sub

So should this work?

Thanks
ps I have posted here a few times but how do I get a "code" box in this message?

 

[dougmarkham]

Thanks guys,
Firstly I don't have macros disabled but valid point
Secondly, I found that macro but had it in the wrong spot. Also I was using the following to untick the CheckBox1:

Private Sub Workbook_open()
CheckBox1 = False

End Sub

So should this work?

Thanks
ps I have posted here a few times but how do I get a "code" box in this message?

Hi Hudco,

By all means give it a go on a test workbook.

There are a few times the macros get disabled: one of which is when the workbook or worksheets are protected from the review tab.
Then you have to add code to disable and re-enable the protection with your code sandwiched between.

URL link showing various code for unprotecting worksheets etc

Sheet1.Unprotect Password:="abc"

'Enter Code / Macro

Sheet1.Protect Password:="abc"

Of course, if you protect a worksheet in that manner, every macro that operates on it must have the protection disabled / enabled that way.

The other approach is to protect the workbook from damage by making the *.xlsm file read only, so that they have to save as a copy of the workbook.
Make a workbook read-only for everybody but me - Excel Off The Grid

Kind regards,

Doug.

 

[Hudco]

Thanks for that Doug, very helpful.

I will experiment with it.

The template I have has a selection of four "forms" based on what button the user selects. These forms areactually one sheet with rows hidden based on their selection. When the form sheet is "created" the original selection sheet is deleted giving them no access to all the instructions as to the setup. So once the form sheet is created then I need the document to be saved. What I am trying to do is prevent the user opening up the template as the template, change a tick box and save so they don't have to tick the tick box in future. The tick box is part of the Quality Assurance process.

Clyde

 

[sykes]

Hi
Couple of things...
Firstly, I think it's always cleaner to get your workbook ready for next use BEFORE (or as) it's closed, rather than when someone's trying to open it. That way, if there's an issue on-open (not letting code run, for example) your controls are still all set to go - in the mode you want them.
Instead of coding in the workbook_open event, put your code into the workbook_before_close event, then don't forget to save....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("sheet1").CheckBox1.Value = False
If Saved = False Then
    ActiveWorkbook.Save
End If
End Sub

Secondly, to answer your question:

ps I have posted here a few times but how do I get a "code" box in this message?

....If I understand the question correctly, to display code (highly recommended on this forum) precede your code with the word code in square brackets (no spaces), and end your code in exactly the same way but with a forward slash before the word code, so it looks thus: /code (once again, surrounded with square brackets).
It's called BB code - some help can be found here

 

[Hudco]

Hi Sykes,
Somehow I did not see your response before. Thanks for both answers.
The workbook is a template and therefore not saved ready to use again other than on the particular project as an XLSM. So I need the user to tick the box to acknowledge they have read a statement of conditions/requirements.
Clyde