Individual zoomfactor for each sheet

[joshua1990]

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]

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]

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]

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]

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]

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