PDA

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

SamT
09-09-2017, 07:51 AM
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