View Full Version : [SOLVED:] Individual zoomfactor for each sheet
joshua1990
09-08-2017, 06:33 AM
Hey guys!
I am currently working on developing a tool for describing and presenting processes in monetary terms. This tool consists of 5 tables (sheets).Each of these 5 sheets has its own width, which means that I or the user must first correct the sheets before applying them.
Is it possible to use VBA to set the zoom range for each sheet, which in Table1, for example, concentrates the zoom range on column 2 and 10, and in table 2 on column 2 and 12?
An individuell zoomfactor for each sheet with regard to the respektive user (formats).
I hope I could represent the problem with my words (:
Best regards!
I hope I could represent the problem with my words.
Edit:
I think this is maybe a possible approach:
Worksheets("Tabelle1").Range("B3:K3").Select
ActiveWindow.zoom = True
But I don't know how to apply this on/ for each sheet immediately after starting/ opening the tool
Paul_Hossler
09-08-2017, 06:49 AM
In the ThisWorkbook code module add these event handlers, changing / adding as required
The column widths are hard coded for each sheet, but there's probably a way to make the macros more elegant depending on data structure
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Sheet1"
Columns("B:I").Select
ActiveWindow.Zoom = True
Range("B1").Select
Case "Sheet2"
Columns("B:K").Select
ActiveWindow.Zoom = True
Range("B1").Select
End Select
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
ActiveWindow.Zoom = 100
End Sub
mdmackillop
09-08-2017, 07:27 AM
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim r As Range
On Error Resume Next
Set r = Range(Sh.ListObjects.Item(1))
If Not r Is Nothing Then
r.Select
With ActiveWindow
.Zoom = True
.ScrollRow = r.Cells(0, 1).Row
.ScrollColumn = r.Cells(0, 1).Column
End With
r.Cells(0, 1).Select
End If
End Sub
joshua1990
09-09-2017, 03:54 AM
In the ThisWorkbook code module add these event handlers, changing / adding as required
The column widths are hard coded for each sheet, but there's probably a way to make the macros more elegant depending on data structure
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Sheet1"
Columns("B:I").Select
ActiveWindow.Zoom = True
Range("B1").Select
Case "Sheet2"
Columns("B:K").Select
ActiveWindow.Zoom = True
Range("B1").Select
End Select
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
ActiveWindow.Zoom = 100
End Sub
Thanks a lot!
This works great!
But now it's updating the zoom factor every time I switch the sheets. Is it possible, to start this procedure only as an initial action? Just at the start/ opening of the file?
Paul_Hossler
09-09-2017, 05:00 AM
1. Added Mac's code
2. Put in ThisWorkbook, add your sheets and ranges, and see if it works for you
Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
With ws
Select Case ws.Name
Case "Sheet1": Call ZoomFit(.Range("B:I"))
Case "Sheet2": Call ZoomFit(.Range("B:K"))
End Select
End With
Next
Application.ScreenUpdating = True
End Sub
Private Sub ZoomFit(r As Range)
r.Parent.Select
r.EntireColumn.Select
With ActiveWindow
.Zoom = True
.ScrollRow = r.Cells(1, 1).Row
.ScrollColumn = r.Cells(1, 1).Column
End With
r.Cells(1, 1).Select
End Sub
Just my personal opinion, but this is a situation where it is more appropriate to encode each sheet individually.
Ie:
Sheet_Activate()
Zoom As Desired
End
Sheet_Deactivate()
UnZoom if needed
End
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.