VBA to auto-resize worksheet to fill the screen

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, this is my second attempt to solve this, the last one ended badly. By badly, I mean with me just a confused simpleton. I'm thinking that what I am trying to do can't be all that difficult but I can't get it to work. Could really use some help here.

I have a workbook with 12 worksheets, all of them are of varying widths as far as the number and size of the columns. My goal is to have the workbook open on anyone's computer and 'fill the screen' horizontally, all the columns with data from left to right, no white space, especially to the right side. I don't care about the vertical aspect because the users can easily scroll up and down. I just don't want them to have to scroll left or right. I found this little piece of code but when I email the workbook to a different computer, the columns with data are all 'selected' but there are still five empty columns to the right.
Code:
Private Sub Workbook_Open()
With Sheets("Sheet18")
Columns("A:L").Select
ActiveWindow.Zoom = True
Range("A1").Select
End With
End Sub
Also, I have 12 worksheets that are using anywhere from 5 to 20 columns so I'm not sure how to incorporate that fact into the code either. I was just trying to get it to work with this one worksheet to start. Any help is much appreciated. Thanks!
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
I've recorded a macro to select the cells from Column A to the right so if there is more than column L it will include them. If you add the code behind each sheet you want to zoom to then it will work for you. Start by right clicking one of the sheets and select View Code then change the two drop down to do use the following, then whilst in the VBA screen just double click each sheet name and add the code so the code will be behind each sheet.

VBA Code:
Private Sub Worksheet_Activate()
Range(Selection, Selection.End(xlToRight)).Select
    ActiveWindow.Zoom = True
    Range("A1").Select
End Sub
With this code, two of the worksheets were zoomed as large as they could get and the rest of them were zoomed as small as they could get. I'm confused...
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak I am getting a 'compile error, expected function or variable'. The 'private sub workbook' line is highlighted in yellow and the '.GoTo' is highlighted in blue.
Strange!

Do you have any other code in the ThisWorkbook module ?

This is the code. Try copying and pasting it in the thisworkbook module as is :
VBA Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim lRightMostColumnIndex  As Long
 
    Application.ScreenUpdating = False
 
    With Sh
        lRightMostColumnIndex = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        .Range(.Columns(1), .Columns(lRightMostColumnIndex)).Select
        ActiveWindow.Zoom = True
        Application.Goto .Range("A1"), True
    End With

End Sub

If the above code doesn't work, I suspect there must be something going on elsewhere in your vba project.

Can you upload a copy of your workbook to file sharing site and post a link here so we can take a look ?
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak Hi Jaafar, thanks for getting back to me. To answer your question, no, I do not have any other code in the ThisWorkbook module. I will try again and report back to you.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Jaafar Tribak ok, this time I did not get the error so I suspect I did something wrong the first time. I might have copied your code into Workbook_Open instead of Workbook_SheetActivate...?? Anyway, no error now but the code is doing some interesting things. As I said, there are 12 worksheets (that are visible to my users) and another 8 that are VeryHidden. The hidden worksheets are PowerQuery's to pull data from a website that I control.

I think we might be on the right track but let me tell you what I am seeing now by worksheet for the ones that are visible to the users...

Sheet19 is zoomed to almost as large as it can get and it's the only one like that which is very strange. There is an image that I am using as a background because this worksheet is my 'Home' page, just trying to make it look appealing but it only covers A1:Q22.
Sheets 1, 5, 11 & 18 all have 3 columns showing on the right side.
Sheets 9 & 12 have 1 column showing
Sheet3 has 10 columns showing
Sheet10 has 11 columns showing
Sheets 4, 6, & 8 are all zoomed horizontally perfectly (this is what I was hoping for with all the worksheets.

I did as you suggested in post #8 and I went through each worksheet to make sure there was NO formatting to the right side of the last column used. Any ideas?
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
Difficult to tell without seeing the workbook.
Maybe someone else in the forum can see this thread and figure out what might be causing the worksheets view not to adjust.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,011
Members
417,062
Latest member
CM214

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