Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sizing Excel Window for any Screen Type

0

Hello -

I have a VBA Macro to adjust the height and width of the Excel window whixh allows me to view just the amount of Data of the Excel sheet that I want. 

I would like to share this Excel file with others who will have a differnt type of monitor or laptop screen than me so the height and width that works for my monitor probably won't be the correct width and height for someone else's monitor.or  screen to see just that iData I want displayed.

Is there a VBA code that allows for a consistent height and width deapite difference is monitor resoution and sixe etc?

Here isi my current code:

sub size()

Application.WindowState = xlNormal

    Application.Width = 896

    Application.Height = 394.5

    End Sub

Thank you

excel
posted by: marko14 (rep: 8) on Tue Mar 28, 2023 at 12:26 pm
Answer
Discuss

Discussion

Hi Marko.

You didn't attach a file but do your width and height values relate to particular rows and columns? 
John_Ru (rep: 6142) Mar 28, '23 at 2:31 pm
Hi John -
I tried to send the file through a new dsicussion but could not as I can only have 1 quewtion per day.
I will send it again tomorrow. 
I have a Macor running each time the Workbook opens and I'd like the workbook to open with the same size as (hopefully) it is when you open it. There is Macro in Module 2 to Hide all scol bars and riibbons, everything except the tabs, I have a size Macro in Module 3 that is activated by ctr+q. I'd like this look for anyone who opens the Workbook to see the same. Thgere is one row under the last numbers 6   1  1 the width should be jsut to the right of the 2 blue columns on the far right. Thanks Mark xxx [Edit] Phone number removed. (better not to invite internet spam ;P)
marko14 (rep: 8) Mar 28, '23 at 5:08 pm
Mark. You didn't tell me the row and column numbers to display and I can't see the "2 blue columns" for example without a file! You need to EDIT your original question to add a file (you can't do that in a Discussion post).

Also, what screen resolution are you using? 

P.s. Suggest you also edit your Discussion point above to remove your phone number- we don't call users (especially me since I'm in the UK) or use private mail.
John_Ru (rep: 6142) Mar 28, '23 at 5:42 pm
@John_Ru, Congratulaions on climbing to the top of the leader board! Dedication and hard work has its rewards.
Cheers   :-)
WillieD24 (rep: 557) Mar 28, '23 at 8:47 pm
@Willie - thanks for that. It really was a climb. Hope you can better me in time (I may quit some time).

@Mark - Willie has provided a well-worked answer below. If it works for you, please mark it as selected. If not (and I'm not sure it's what I thought you wanted), please comment on it and post a file as I suggested above.
John_Ru (rep: 6142) Mar 29, '23 at 4:16 am
Hi Willie and John -
Thanks so much for your tme and help with the code. I am not that proficient with VBA codes and modules but will take Willie's code and let you know how it comes out.

One other question, if I may: do you know or any way to email an xlsm file so the recipient doesn't have to create a folder through their Trust Center settings  in order to run Macros on the Excel xlsm workbook.I emaial them.
I'd like to senf an xlsm Workbook to people who aren't too familiar with Excel  so I'd like to make it as simple as possoble for them to use Macros.
Thanks again,
Mark
marko14 (rep: 8) Mar 29, '23 at 3:22 pm
Mark. I'm reluctant to advise on emailling .xlsm files you make, given your proficiency in VBA is limited at present - for this reason and other reasons corporate IT rules may prevent recipients from enabling macros or even receiving the files you attach to your email. 

Note that if your file must be read macro-enabled, you might like to use the approach in Don's tutorial 
Force a User to Enable Macros in Excel - Advanced Version.

Haven't seen your file but even if you use Willie's code, users probably will not thank you for altering their default app window. Before you email your file, I'd recommend that you capture the user's settings (including zoom) so you can restore them when your file is closed. 
John_Ru (rep: 6142) Mar 29, '23 at 5:37 pm
Add to Discussion

Answers

0
Selected Answer

Hi Marko,

You might want to try the following. In a separate regular code module copy the following:

Declare Function GetSystemMetrics32 Lib "User32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

In another module copy the following code and run it with the file open to get the Width and Height of the file when open on your machine.

Sub App_Width_Height()

' macro written by Willied24

Dim AppW As Long
Dim AppH As Long
Dim AppTop As Long   ' distance from top of screen
Dim AppLeft As Long   ' distance from left edge of screen

AppW = Application.Width
AppH = Application.Height
AppTop = Application.Top
AppLeft = Application.Left

MsgBox AppW & "  wide   x   " & AppH & "  high"

MsgBox "Top =   " & AppTop & "    Left  =   " & AppLeft

End Sub

Then in the "ThisWorkbook" code window, at the top left select "Workbook". It will default with Workbook_Open(). Copy and paste the following:

' macro written by Willied24 (Mar-2023) for teachexcel.com

Dim wM As Long, hM As Long   ' screen resolution of my machine
Dim wY As Long, hY As Long   ' screen resolution of user's machine
Dim srcW As Long, srcH As Long  ' width and height of Excel window on the source (your) machine
Dim openW As Long, openH As Long   ' width and height values to open window at
'
' edit the following two lines to match your screen resolution size
wM = 1366
hM = 768
'
' get user's screen resolution sizes
wY = GetSystemMetrics32(0) ' width (in pixels)
hY = GetSystemMetrics32(1) ' height (in pixels)

' these values were used for testing purposes
'wY = 1000
'hY = 500

' edit the following to match the file's W & H sizes when open on your machine
srcW = 1030
srcH = 582
' calculate width and height values
openW = srcW * (wY / wM)
openH = srcH * (hY / hM)
'
'' set application sizes to open at
With ActiveWindow
 .WindowState = xlNormal
 ' distance from top and left of screen
 .Top = 1
 .Left = 1
 ' width and height of application window
.Width = openW
.Height = openH
End With

It compares the screen resolution of the users machine to that of yours (which is hard coded in the code - edit to your setting). Then some math is done to set the width and height to open. (example file with code also attached)

Hope this helps; if so, please mark my answer as selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Tue Mar 28, 2023 at 8:43 pm
Discuss

Discussion

@Willie Just noticed your code includes the comments:
' get user's screen resolution sizes
wY = GetSystemMetrics32(0) ' width in points
hY = GetSystemMetrics32(1) ' height in points
but doesn't the GetSystemMetrics32 return sizes in pixels (rather than points)?
John_Ru (rep: 6142) Mar 29, '23 at 12:53 pm
@John,   nice catch; Thanks. I have corrected it above. I assembled this code from things I know and assorted pieces I Googled. I corrected a couple other errors/typos but missed this one.
WillieD24 (rep: 557) Mar 29, '23 at 1:13 pm
Willie -
Thank you again for taking the time to write out this code. I opened your atrtached Excel File but got the folliwing error message:
Complie error:
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and the mark them with the PtrSafe attribute.

I am not at all familiar with VBA code so I'm sorry but I'm lost with that message. I don't know how to attach a file to reply message in the this Forum so I will create a new question and attach my Workbook that, hopefully, opens to the width and height I would like.it to open on anyone else's Monitor or Laptop screen.
With this Workook:
Module 2 - ctr M sizes hides all Excel Ribbons, row and colum headings etc and just leaves the workbook tabs.
Module 4 - ctr Y shows all those items that were hidded.
I already have some code in the This Worbook folder - it is the sams as Module 2. John Ru wrote the workbook_open() code.

Ideally I would like  my Workbook to get to the correct Width and Height upon opening by any other user whith whom I share the workboo.

Thank yu for you time once again.
Mark Olthoff
marko14 (rep: 8) Apr 2, '23 at 7:40 pm
@Willie - in case you don't notice, Mark has attached his file to the new question "Excel Window Sizing". 
@Mark - did you have trouble attaching your file to this question? (That's preferable and can be done when you edit the original question.)

Also please don't forget to respond to my answer on sharing / navigating an Excel file online. 
John_Ru (rep: 6142) Apr 3, '23 at 3:29 am
@Mark, I don't know why you are getting the Compile error. My laptop and desktop are both 64-bit and the file opens on both machines without any errors.
I had a quick look at your MLB.xlsm file and it is difficult to navigate when you hide everything that you do (scroll bars, headings, etc; but I guess you must have your own reason for doing so) By hiding these things, it applies it to the Excel application and not just the workbook. If you have another workbook open, that workbook will have the same appearance as your MLB workbook - no scroll bars, no headers, etc. Also, in module 2 you have the "Workbook_Open" code as well as in the ThisWorkbook code window. It only belongs in the ThisWorkbook code window. Another thing I noticed is your keyboard shortcuts (macro1 and macro3). The shortcuts Ctrl+j and Ctrl+p are built into Windows for other purposes. I would recommend changing them to something which is not part of Windows to avoid accidentally running a macro. (eg: Ctrl+p is Windows shortcut for Print)
I will have a look again later when I have more time.

Cheers   :-)
WillieD24 (rep: 557) Apr 3, '23 at 10:24 am
@Willie - I agree with all your points, especially about the location of the Workbook_Ope" code being only in the ThisWorkbook code window. Furthermore the code hiding the ribbon etc. (taken from one of Don's tutorials I believe) make it hard to look around Excel and I think it will be frustrating to regular users.

Incidentally I use a 32-bit Excel 365 so haven't seen that compilie error. 

@Mark - I'm not sure about Willie but you haven't really told us the purpose of this file and I'm not sure how much data you want to show users (given the difficulty navigating Excel after the usual methods are hidden)- which tabs/ cell ranges are to be displayed?

I'm suprised that gridlines are shown for a "controlled" view and that several tabs are displayed- are the all to be visible to users?
John_Ru (rep: 6142) Apr 3, '23 at 11:19 am
I apologize for the delay in getting back. My purpose of this Workbook is to track Major League Baseball games on a daily basis and share it with others. I wanted to build a Website and upload the updated statistics, kept in this Workbook, to the site every day. I was hoping that I could upload the Workbook to a Website and give users the ability to access it from their PC as if it were native to their PC – have the ability to use their mouse to hit the tab buttons and navigate around the spreadsheet from the Web. I spent a few hours on the phone with Microsoft yesterday with no luck. We tried to publish the Workbook to the Web as an HTML file but had no luck as too many formulas and data was lost. If you know of any way that this is possible that would be great.   I designed the Workbook to give the user as much information on each page. I have a screen shot of the screen size date and optimal screen size but I can’t see where to attach it from the “add to this discussion screen. All the user needed was: - the tabs on the bottom to navigate to various statistics - Only the Game and FF tabs have multiple pages of data which can easily be accessed by hitting the Page Down button or Home button to move around the pages. I have developed a Key that explains all this to the user.
marko14 (rep: 8) Apr 5, '23 at 1:53 pm
If the Workbook was going to be assessed via a Website that anyone could get to, I wanted to protect each sheet so no changes could be made to any of the pages. Therefore there was no reason to give anyone access to the ribbon. Formular bar etc. Essentially, I was trying to make the Workbook look like a Word Document with tabs at the bottom. I was also going to email the Workbook to some people that may not be Excel savvy, so I wanted to make that as easy as possible for them to access it. I wanted to have their Excel Window sized as mine, that’s where Willie’s code came into play. But, now that I know that the code will change the user’s screen for everything they view on it – that’s not an option. When emailing the Workbook, I wanted to make opening the Workbook as easy as possible. That’s where the Force a User to accept Macro code will probably be very helpful. With John’s help, when I open up my Workbook, it automatically hides everything except tabs and the 2nd Macro .(ctr+Q) sizes it. I’m hoping I can make that happen when I send the Worksheet to others. Thank you again you time.
marko14 (rep: 8) Apr 5, '23 at 1:54 pm
Mark. I know nothing about your favourite sport but you say you want to "... track Major League Baseball games on a daily basis and share it with others". Is that to rival (what I assume are) official sites like 
Baseball Standings and their stats? It sounds quite ambitious and I couldn't really help you with website. Not sure if Willie can help you further but I'll leave it to him to volunteer if he can
John_Ru (rep: 6142) Apr 5, '23 at 4:22 pm
Just a thought, could you not just use my Answer to Viewing Excel Workbook on a website
 and publish a protected workbook? Users get read-only access and can navigate via tabs anyway. 
John_Ru (rep: 6142) Apr 5, '23 at 4:31 pm
Mark,
I have to agree with John that there are many existing websites which provide stats and using one of those might be the better way to go. Working with (viewing) an Excel file on line is not part of my wheelhouse so I can't provide any help there. From the information I found with a Google search, veiwing online is not as simple as you are hoping for. The file needs to be uploaded to a site such as OneDrive for example. Then, anyone who wants to view the file must be able to access OneDrive. Another way to go is that each user needs to install a "viewer" app on their device. See how it is becoming complicated. For these reasons I won't be able to provide any further assistance. Sorry.
Also, I would recommend only hiding the ribbon (and maybe the formula bar) if your goal is to make more of the worksheets visible. 
Maybe someone else will be able to provide you with more help.

Cheers   :-)
WillieD24 (rep: 557) Apr 8, '23 at 10:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login