Macro to Activate on opening Workbook

Hudco

Board Regular
Joined
Jan 4, 2006
Messages
114
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Hi Hudco,

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

VBA Code:
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
but if the user has macros disabled, then the code won't work
 

Hudco

Board Regular
Joined
Jan 4, 2006
Messages
114
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

Board Regular
Joined
Jul 19, 2016
Messages
234
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

Board Regular
Joined
Jan 4, 2006
Messages
114
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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....
Code:
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

Board Regular
Joined
Jan 4, 2006
Messages
114
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,031
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top