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!

 

[Michael M]

You will have to unprotect the sheet, save the new changes then reprotect the sheet

 

[CRE_finance_guy]

You will have to unprotect the sheet, save the new changes then reprotect the sheet

Thanks for the response. I figured it out. Turns out the ScrollArea feature doesn't save. The only way to get it to work when you reopen the sheet is with a macro.

 

[Michael M]

Well, you could put the scroll area required into a Workbook_open macro, so that it sets it every time the workbook is opened

 

[Michael M]

Maybe like this

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:F100" 'change the sheet name and range to suit your needs
End Sub

 

[CRE_finance_guy]

Maybe like this

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:F100" 'change the sheet name and range to suit your needs
End Sub

Interesting. I'll give this a try.

 

[CRE_finance_guy]

So this doesn't work, which I'm guessing might be because I'm doing something wrong. I opened the developer window, and under "Microsoft Excel Objects" my sheets are all listed, along with "ThisWorkbook". I right-clicked on "ThisWorkbook", then went to insert and module. I pasted it there, and then hit save. I then closed and re-opened the workbook, but it didn't work. So then I did the same process, but inserted the code into the specific sheet that I want to stop the scrolling on. Again I closed and re-opened the workbook, but it didn't work. Any thoughts on why this could be? Is it working for you? Thanks again for the help.

 

[uk747]

When you save the file, are you saving it as .xlsm macro document, or are you saving it as .xlsx which won't save code

 

[CRE_finance_guy]

When you save the file, are you saving it as .xlsm macro document, or are you saving it as .xlsx which won't save code

I just checked to make sure, and it's saving as an .xlsm file.

 

[CRE_finance_guy]

Yeah I can't figure this out. I've changed the sheet name in the code from "Dashboard" to it's actual sheet name ("Sheet2"), and I tried it as a regular sub instead of a private sub. I also noticed online someone suggested to change the beginning of the code from Private Sub Workbook_Open() to Private Sub Workbook_WindowActivate(ByVal Wn As Window), but that doesn't work either.

Lastly, the sheet in question is protected, so I thought that might have something to do with it. So I tried the code on an entirely different worksheet in a completely different Excel file with no protected cells. I saved it as an .xlsm file first too. Still no luck.

Getting this macro to run upon opening the worksheet seems like it should be pretty simple, but there's something about this particular ScrollArea code that prevents it from working.

This is a real head-scratcher. If you or any VBA specialists can figure this out I'd love to hear more ideas. Thanks again!