Preventing Zoom/Pan/Scroll

[JCamara]

Howdy.

I've been researching and looking for a neat way to avoid users zooming, panning and scrolling. So far, refering to zoom, I found ways of "fixing" it, after a given action (change selection)

The code I'm actually using is the following (inside the specific worksheet's, that i want to lock, code). The worksheet I want to lock is called "DASHBOARD"

Private Sub Worksheet_Activate()
If ActiveSheet.Name = "DASHBOARD" Then




ActiveWindow.Zoom = 100
Range("A1").Activate
End If


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("DASHBOARD").ScrollArea = "A1:BK100"


If ActiveSheet.Name = "DASHBOARD" Then
Do
 If ActiveWindow.Zoom <> 100 Then
    ActiveWindow.Zoom = 100
 End If
 DoEvents
Loop While True
End If


End Sub

What I don't get is that, even with the logical test of the worksheet's name before any action and the code itself being inside the worksheet, I can't restrain the code to only one sheet. It "fixes" the zoom on every active worksheet.

Another thing I'm unhappy with is that I've lost the "Ctrl+Z" function. I get that macros can't be undone via "Ctrl+Z", but is there any other way around?

Anyway, thanks in advance!

 

[dchaney]

You should be able to do what you want with the following code, just make sure you paste this in the Dashboard worksheet, anytime that sheet is activated it will run this code. Several differernt ways to do this, others may have a different approach.

Private Sub Worksheet_Activate()

Sheets("DASHBOARD").ScrollArea = "A1:BK100"
ActiveWindow.Zoom = 100


End Sub

 

[JCamara]

Yeah, thanks, but that wouldnt prevent users from chaging zoom.

Is there anyway to make Worksheet_SelectionChange work without disabling my ctrl+Z

and

WHY THE HELL does it fixes other worksheets zoom if i specifically wrote the code inside the DASHOBOARD whorksheet with a logical check on ws name?!?!?


Thanks anyway

 

[JCamara]

Yup, got it. At least the other sheets doesn't get changed.

I was missing the loop. The loop being inside the condition (sheet name) check it'll start and keep looping based o zoom stat even if trigger condition has changed. what I needed was to control the actions in-loop based on sheet's name. So now it is like this:

Private Sub Worksheet_Activate()


If ActiveSheet.Name = "DASHBOARD" Then


    ActiveWindow.Zoom = 100
    Range("A1").Activate
    
    Do
        If ActiveWindow.Zoom <> 100 And ActiveSheet.Name = "DASHBOARD" Then
            ActiveWindow.Zoom = 100
            Sheets("DASHBOARD").ScrollArea = "A1:BK100"
        End If
        DoEvents
    Loop While True


End If


End Sub