Preventing Zoom/Pan/Scroll

JCamara

New Member
Joined
Jan 14, 2015
Messages
38
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"

Code:
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
732
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
Private Sub Worksheet_Activate()

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


End Sub
 

JCamara

New Member
Joined
Jan 14, 2015
Messages
38
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

New Member
Joined
Jan 14, 2015
Messages
38
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:

Code:
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
 

Forum statistics

Threads
1,141,060
Messages
5,704,042
Members
421,324
Latest member
Devo182

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top