Locking a Sheet

[CRE_finance_guy]

My model has a dashboard page full of graphs, and I'd like to lock it, so it's just a single image with no functionality. In other words, the cells aren't selectable, the charts aren't selectable, and scrolling is disabled. I have been able to achieve this (with Properties and ScrollArea, and protecting the sheet), but then I found out that the changes don't save.

Does anyone know how to achieve this in a way that actually saves?

Thanks for any help. Much appreciated!

 

[CRE_finance_guy]

Using my Excel/VBA-environment I don't have issues like yours. The Workbook Open event handler fires and the scroll area is limited as desired. I was unable to reproduce the problems you described.

The only way to find out whether it is specific to that particular workbook or to (the installation of) the Excel version you are using is to create a new, empty workbook. First save it as XLSM, then within the VBE open the code module of ThisWorkbook and use the drop downs to generate a Workbook_Open procedure and then include just one statement in it: Stop.
Save and close the workbook, then open it again. If the VBE suspends code execution and switches to break mode (with a yellow line on the Stop statement) the workbook with your dashboard is causing the issue you're experiencing. If nothing happens then there must be something wrong with your Excel/VBA-environment.

For the moment I couldn't think of any other cause than the ones mentioned in my previous post.

Thanks GWteb. I tried it again, but with the statement just being stop "stop". I saved it (in a new macro-enabled workbook), closed the workbook, and reopened it. I opened up the code again and didn't see any yellow lines. Here's what it looks like:

 

[CRE_finance_guy]

Using my Excel/VBA-environment I don't have issues like yours. The Workbook Open event handler fires and the scroll area is limited as desired. I was unable to reproduce the problems you described.

The only way to find out whether it is specific to that particular workbook or to (the installation of) the Excel version you are using is to create a new, empty workbook. First save it as XLSM, then within the VBE open the code module of ThisWorkbook and use the drop downs to generate a Workbook_Open procedure and then include just one statement in it: Stop.
Save and close the workbook, then open it again. If the VBE suspends code execution and switches to break mode (with a yellow line on the Stop statement) the workbook with your dashboard is causing the issue you're experiencing. If nothing happens then there must be something wrong with your Excel/VBA-environment.

For the moment I couldn't think of any other cause than the ones mentioned in my previous post.

By the way, I'm working from a 3-month old laptop, and I'm getting all my office products from GoDaddy (Office 365). So my version of Excel should be 3 months old at the very most.

 

[Michael M]

As mentioned earlier, did you try it on a "clean" workbook to see if it also fails
AND
also, as mentioned earlier have you considered uploading the workbook to a hosting site such as Dropbox, then posting a link to it back here...making sure it is cleaned of sensitive data of course.

 

[GWteB]

I opened up the code again and didn't see any yellow lines.

If the event handler was fired by Excel, the VBE should have been opened automatically.
Since it didn't, we now know for sure this issue is not caused by a specific workbook, the Workbook Open event handler is ignored by Excel anyway and for whatever reason.

 

[GWteB]

@CRE_finance_guy, the screenshot attached to your post #41 tells me, that you did not follow my instructions, so forget about my post #44.
The code is supposed to be in the ThisWorkbook module, not in a standard module.

EDIT:
(image attached)

 

[CRE_finance_guy]

Has THAT been the problem this whole time? I thought I was putting the code into "ThisWorkbook". Here's what I've been doing: I right-click on "ThisWorkbook", then "insert", then "module".

 

[Michael M]

 

[GWteB]

Here's what I've been doing: I right-click on "ThisWorkbook", then "insert", then "module".

You were supposed to choose "View code" instead of "Insert module" after right clicking. I would suggest that you familiarize yourself a little more with using the VBA editor.
Have you already achieved the desired result with one of the suggestions posted here?

 

[CRE_finance_guy]

You were supposed to choose "View code" instead of "Insert module" after right clicking. I would suggest that you familiarize yourself a little more with using the VBA editor.
Have you already achieved the desired result with one of the suggestions posted here?

My apologies. Thank you, GWteB. You were so helpful. Much appreciated. I've got it now.

 

[GWteB]

Glad we could be of some help & thanks for letting us know.