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]

I don't have Excel ATM...but did you change the ranges and sheet name?
Can you upload the workbook to Dropbox or similar sharing sites then post the link back...I'll then have a look at it

 

[Michael M]

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).

Your scroll area is only applied to one cell...ScrollArea = "$L$6"....it needs to be the entire required range of cells

 

[Fluff]

@CRE_finance_guy in future please do not duplicate you threads. On this occasion I have them together.

 

[Jaafar Tribak]

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).

Two questions:

1- Does it work if you set the scroll area manually via the Sheet Properties window in the VBEditor ?

2- Type the following line Sheets("Dashboard").ScrollArea = "$L$6" in the Immediate window and press the Enter key.
Does the above step work ?

 

[Jaafar Tribak]

Also, Try setting the scroll area via the Properties collection of the VBComponent as follows :

In a Standard Module:

Sub Test()
    ThisWorkbook.VBProject.VBComponents(Sheets("Dashboard").CodeName).Properties("ScrollArea") = "$L$6"
End Sub

Note that you first need to allow access to the vba project object model via the : Developer tab > Macro Security > Macro Settings > Trust Access to the vba project object model.

 

[CRE_finance_guy]

Your scroll area is only applied to one cell...ScrollArea = "$L$6"....it needs to be the entire required range of cells

No, as I mentioned, the code works perfectly when I run it. The problem isn't with the fact that I'm only listing one cell as the scroll area. It's that the code will not execute when the user opens the workbook. (And, incidentally, I actually have changed it to a proper range, just to make sure that wasn't the problem. It has no effect.)

 

[Michael M]

If the code is in the This workbook module and doesn't execute, it would suggest that the other user doesn't have macros enabled !
Does it work on you machine ??

 

[CRE_finance_guy]

Two questions:

1- Does it work if you set the scroll area manually via the Sheet Properties window in the VBEditor ?

2- Type the following line Sheets("Dashboard").ScrollArea = "$L$6" in the Immediate window and press the Enter key.
Does the above step work ?

1. Absolutely. It works perfectly if I manually select sheet properties and enter the scroll area. Additionally, when I use the VBA code, and hit the play button, that also works.
2. Yes, I typed this ("Sheets("Dashboard").ScrollArea = "$L$6") into the immediate window, hit enter, and it works.

So there's no problem with the ScrollArea functionality. The only problem is that the code will not execute upon opening the workbook.

 

[CRE_finance_guy]

@CRE_finance_guy in future please do not duplicate you threads. On this occasion I have them together.

The second post is actually different. It was about how to get this VBA code to work. Still no answers, but I'm really appreciating people trying to help out. Thanks to everyone for the effort. I'm convinced there must be a solution here. I'm not sure why this code won't execute.

 

[Michael M]

Have you put a breakpoint in the code to see if it is actually firing at open ??