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]

Did you unlock the sheet at workbook open

 

[Michael M]

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

 

[CRE_finance_guy]

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

This doesn't work either. I tried the earlier code on a completely unprotected sheet, and it didn't work on that either, so removing protection isn't the problem. I'm curious, is this working for you?

 

[CRE_finance_guy]

How could such simple code not work? Here's the goal, and the code I'm using. I just need to fix the scroll area of a particular sheet, but the "ScrollArea" feature in sheet properties does not save with a file. Instead, it resets itself every time you re-open a workbook. So I need a VBA solution to simply reset the ScrollArea property to where I want it each time a user opens the file.

Here is the code I've been using:

Private Sub Workbook_Open()
Sheets("Dashboard").ScrollArea = "$L$6"
End Sub

If I run the code, it works. So I know it's functional if I can just get it to run upon opening. However, when I save the file, close the workbook, and reopen it, the code won't run automatically.

This is an XLSM file, the sheet is unprotected, and I've tried inserting the module into "ThisWorkbook" and also into the specific sheet in question (which is "Dashboard"). I've also tried using the actual sheet number (in this case, "Sheet3" instead of "Dashboard").

I'm really hoping there's a VBA pro out there who can tell me what I'm doing wrong. Or maybe there's some weird quirk in Excel causing this particular type of code to not automatically execute upon opening the workbook.

THANK YOU in advance for any help. Much appreciated!

 

[RoryA]

That code has to go into the ThisWorkbook module. It should then run as long as macros are enabled.

 

[CRE_finance_guy]

That code has to go into the ThisWorkbook module. It should then run as long as macros are enabled.

Yeah, as I said, I tried that already. I put it in ThisWorkbook, and then when that didn't work I put it in the actual sheet. Neither way works.

I also just read another thread on this forum whose author has the identical problem. Evidently no solution was found for him either. With all the VBA gurus on this site I'm really surprised that there's no solution to this.

Below is the other thread I'm referring to. I'm starting to think this might be some kind of glitch in Excel. Limit scroll area permanently

 

[Jaafar Tribak]

What happens when you run the code in other than the Workbook_Open event handler ? like if Sheets("Dashboard").ScrollArea = "$L$6" is executed from a SUB in a standard module ? Does it work then ?

 

[Jaafar Tribak]

Will this work for you :
In the ThisWorkbook Module

Private Sub Workbook_Open()
    Application.OnTime Now, Me.CodeName & ".SetScrollArea"
End Sub

Private Sub SetScrollArea()
    Sheets("Dashboard").ScrollArea = "$L$6"
End Sub

 

[CRE_finance_guy]

Will this work for you :
In the ThisWorkbook Module

Private Sub Workbook_Open()
    Application.OnTime Now, Me.CodeName & ".SetScrollArea"
End Sub

Private Sub SetScrollArea()
    Sheets("Dashboard").ScrollArea = "$L$6"
End Sub

Hi Jaafar. Thanks for the thought. Unfortunately this doesn't work either.

 

[CRE_finance_guy]

What happens when you run the code in other than the Workbook_Open event handler ? like if Sheets("Dashboard").ScrollArea = "$L$6" is executed from a SUB in a standard module ? Does it work then ?

I've tried to run this code in both the ThisWorkbook, and also the worksheet in question, and I've also removed "private", so that it just starts with "Sub Workbook_Open()". None of it works. Have you tried this code yourself? Maybe there's something wrong with my version of Excel (I'm using the latest version).