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!

 

[GWteB]

It seems obvious, but are Events enabled at all?

Sub EE()
    Excel.Application.EnableEvents = True
End Sub

 

[Jaafar Tribak]

As Michael M asked, does the Workbook_Open event fire at all when opening the workbook ? try adding a MsgBox to the Workbook_Open event handler to verify that it does.

Anyway, If the Workbook_Open fires as expected but still the scroll area is not set despite all that has been tried so far then try this last following workaround. If this last workaround doesn't work either , I don't know what will:

1- Add a new Standard Module and place the following code in it :

Option Explicit

#If VBA7 Then
   Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
   Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
#Else
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
#End If

Public Sub SetScrollArea()
    Call SetTimer(Application.hwnd, 0, 100, AddressOf SetScrollAreaNow)
End Sub

Private Sub SetScrollAreaNow()
    Call KillTimer(Application.hwnd, 0)
    Sheets("Dashboard").ScrollArea = "$L$6"
End Sub

2- In the ThisWorkbook Module:

Private Sub Workbook_Open()
    Call SetScrollArea
End Sub

 

[CRE_finance_guy]

It seems obvious, but are Events enabled at all?

Sub EE()
    Excel.Application.EnableEvents = True
End Sub

Hi GWteB. Yes, events are enabled.

 

[CRE_finance_guy]

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 ??

Hi Michael. Good thought. But no, this isn't the issue. The code does not work on my machine. Macros are absolutely enabled. Again, when I paste the code, and click play, it sets the scroll area just as it should. But then I save the workbook and close it, and reopen it, and the code will not run automatically upon my reopening the workbook. If I get into the VBA window and click play again, it will set the scroll area properly again. So the code works when I click run, but it just won't run automatically when I open the workbook.

 

[CRE_finance_guy]

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

This is interesting. How would I test this? Since the code works whenever I manually run it, my guess is that it's just not running upon opening. Let me know. Thanks Michael. I really appreciate your (and everyone's) help here!

 

[Michael M]

click in the LH border so that a brown dot appears next to this line
When you open the workbook, if the code is firing the line will be yellow

 

[GWteB]

Most of the foregoing suggestions depend on the Workbook Open event. As it looks now, the associated handler is not executed by Excel for whatever reason.
To check that assumption, I'm afraid @Michael M's proposal won't work because breakpoints are not saved in a workbook's VBA project after saving and closing. That's where the Stop statement comes in handy, which suspends code execution and switches to break mode.

Private Sub Workbook_Open()
    Stop
End Sub

Perhaps the cause of @CRE_finance_guy's issue can be found in a (partly) corrupt workbook or a workbook that was originally an (old format) .XLS workbook and was converted later.

That said, I'd like to note that an existing Workbook_Open() or Auto_Open() event handler is not executed when the workbook is opened from Excel while holding down the Shift key. This applies to both manually opened workbooks and workbooks that are opened by means of a macro. This can occur when using Windows' stickey keys feature or when the workbook is opened by a macro executed by a keyboard shortcut using the Shift key.

If it turns out that no cause can be found, this issue may be circumvented with the code below. My approach consists in that the code, if necessary, makes any worksheet - other than the one whose scroll area is to be set - active immediately prior to saving. As a result, this worksheet will also be active when the workbook is opened. If the relevant dashboard worksheet is subsequently activated, the event handler will still set the scroll area if necessary. Needless to say, this approach can only be used if the workbook consists of multiple sheets.

This goes in the ThisWorkbook module:

Option Explicit

Public OnOpenCompleted  As Boolean

Private Sub Workbook_Open()
    LimitScrollArea
    Me.OnOpenCompleted = True
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    LimitScrollArea
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    SwitchSheet True
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    SwitchSheet
End Sub

This goes in a standard module:

Option Explicit

Public Const DASHBOARDSHEET As String = "DashBoard"  ' <<<< name of sheet of which scroll area is to be limited; change name to suit

Public ScrollIsLimited  As Boolean
Public CurrentSheet     As Object


Public Function IsDashboard(ByVal argSht As Object) As Boolean
    IsDashboard = VBA.StrComp(argSht.Name, DASHBOARDSHEET, vbTextCompare) = 0
End Function

Public Sub LimitScrollArea()
    If Not ScrollIsLimited Then
        ThisWorkbook.Sheets(DASHBOARDSHEET).ScrollArea = "$L$6"
        ScrollIsLimited = True
    End If
End Sub

Public Sub SwitchSheet(Optional ByVal argBeforeSave As Boolean = False)
    If Not ThisWorkbook.OnOpenCompleted Then
        Excel.Application.EnableEvents = False
        If argBeforeSave Then
            Set CurrentSheet = ThisWorkbook.ActiveSheet
            Excel.Application.ScreenUpdating = False
            If IsDashboard(CurrentSheet) Then
                ThisWorkbook.Worksheets("Sheet2").Activate    ' <<< change sheet name to suit
            End If
        Else
            If Not CurrentSheet Is Nothing Then
                CurrentSheet.Activate
            End If
            Excel.Application.ScreenUpdating = True
        End If
        Excel.Application.EnableEvents = True
    End If
End Sub

 

[CRE_finance_guy]

click in the LH border so that a brown dot appears next to this line
When you open the workbook, if the code is firing the line will be yellow
View attachment 55544

Hi Michael. So I tried this, but when I re-opened the sheet and re-opened the VBA window, the code wasn't in yellow. See image below:

 

[CRE_finance_guy]

Perhaps the cause of @CRE_finance_guy's issue can be found in a (partly) corrupt workbook or a workbook that was originally an (old format) .XLS workbook and was converted later.

Hi GWteB. This theory sounded like a possibility, since the financial model in question was probably originally created in an older version of Excel. So I started a brand new file, then saved it as a macro-enabled workbook, and it still doesn't work. I'm curious - is this code working for anyone else? Has anyone else tried this code with success? Or am I the only one that this doesn't work for?

 

[GWteB]

I'm curious - is this code working for anyone else? Has anyone else tried this code with success? Or am I the only one that this doesn't work for?

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.