CODEWITHSUNDEEP
excelvba
Corey
Corey

Reputation: 41

Use Excel VBA to Change View

I'm trying to run a simple For Each loop to change the view in Excel, but keep getting a:

438 Run-time Error

Sub ChangeView()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    ActiveWindow = xlNormalView
    Range("A1").Select
Next

The error highlights the ActiveWindow line. Why?

Upvotes: 3

Views: 14334

Answers (3)

Daryl B
Daryl B

Reputation: 1

You can also do ActiveSheet.Range("A1").Select It won't change the view necessarily but it'll select the appropriate cell as well.

Upvotes: 0

cssyphus
cssyphus

Reputation: 40096

The following VBA will set all SELECTED sheets to the same view, same zoom level and same visible area as the active tab:

Sub ResetAllSheetPerspectives_OnSelectedSheets()
    'Not working? Are any tabs selected? Only works on selected worksheets.
    Dim ws As Worksheet
    Dim lRow As Long
    Dim lCol As Long
    Dim dZoom, dView As Double

    lRow = ActiveWindow.ScrollRow
    lCol = ActiveWindow.ScrollColumn
    dZoom = ActiveWindow.Zoom
    dView = ActiveWindow.View

    For Each ws In Application.ActiveWindow.SelectedSheets
        ws.Activate
        ActiveWindow.Zoom = dZoom
        ActiveWindow.View = dView
        Application.Goto ws.Cells(lRow, lCol), True
    Next ws
End Sub

Please upvote the below source:

Setting all selected sheets to same visible area

See Also:

How to run a macro in Excel - support.office.com

Use macros in Excel - WikiHow

How to use macros you find online in six easy steps - searchengineland.com

Upvotes: 0

David Zemens
David Zemens

Reputation: 53653

ActiveWindow is an object, xlNormalView is a long/enumerated constant associated with a property of that object. You can't assign the property value to the object itself, instead, assign it to the appropriate property. I believe the correct one would be:

ActiveWindow.View = xlNormalView

Upvotes: 8

Related Questions