Protecting a sheet upon closing without VBA...

orilion

New Member
Joined
Apr 2, 2015
Messages
9
Hello,

So, I've got this problem. I need to make sure a worksheet gets protected once the workbook is closed, which is fairly using VBA... Not when macros are disabled, however...
The user opens the file, unprotects the sheet, closes the file and... the sheet remains unprotected because the macro didn't run...

I have got no idea how to proceed...

Many thanks for any suggestions!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
What I have seen people do is highlight the data sheet that people need to work with, by default. So, just a blank sheet is displayed when they open the file.
Then, they add a few Workbook event procedures codes to the workbook.
1. Workbook_Open: unhides the data sheet(s) that they need
2. Workbook_BeforeClose: rehides the data sheets(s) and saves the workbook before closing

If you make the sheets very hidden, they will not be able to unhide and see the data sheets that they need unless they enable macros.
So essentially what you are doing is trying to render the workbook worthless unless they enable macros.
You can add some text to the blank sheet you are showing that says something like "You must enable macros on this workbook in order to work with it".
 

orilion

New Member
Joined
Apr 2, 2015
Messages
9
What I have seen people do is highlight the data sheet that people need to work with, by default. So, just a blank sheet is displayed when they open the file.
Then, they add a few Workbook event procedures codes to the workbook.
1. Workbook_Open: unhides the data sheet(s) that they need
2. Workbook_BeforeClose: rehides the data sheets(s) and saves the workbook before closing

If you make the sheets very hidden, they will not be able to unhide and see the data sheets that they need unless they enable macros.
So essentially what you are doing is trying to render the workbook worthless unless they enable macros.
You can add some text to the blank sheet you are showing that says something like "You must enable macros on this workbook in order to work with it".
Seems promising. Thanks!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

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