Free Microsoft Excel 2013 Quick Reference

Vba screen resolution Results

Is there some way to change the screen resolution to 800x600 when a workbook is opened and change it back to the previous size when the workbook is closed?

Hi all.

I have an Excel based application that needs to run with a screen resolution of 1024*768 in order to display correctly.

Is it possible to use VBA to detect the users screen resolution.

If the screen resolution is not set to 1024*768 then resize Excel's main window to run at that width and height?

Your help will be greatly appreciated.

Hi there - here is something that no-one seems to be able to do: I am trying to get the screen resolutions on a dual monitor setup in Excel 2002.

GetSystemMetrics32(X) gives me some info, as follows - when X is:
(measurements in pixels by the way)

0 - I get the width of the primary monitor
1 - I get the height of the primary monitor
78 - I get the width of the virtual monitor
79 - I get the height of the virtual monitor

The virtual screen is the 2 screens "added together"

For example, if I have 2 monitors side by side:

Monitor 1: Res of 1680 x 1050
Monitor 2: Res of 1600 x 900

I can use GetSystemMetrics32 to get me the width of the primary monitor (1680) and the virtual monitor (3280), thus deducing the 2nd monitor has a width of 3280-1680 = 1600.

HOWEVER - I can't determine the Y height of the second monitor because the virtual monitor is 1050 (The largest of the 2 values)

My thought re:solutions so far have been to:

a) Somehow set the primary monitor temporarily to 800x600. That way, the Y value of the second monitor would be the Y value of the virtual screen (-ie- 900, as 900 is larger than 600). This is clumsy though, and assumes the second monitor is greater than 800x600.

b) Get the X value for the second monitor first, then somehow set the two screens temporarily one above the other (vertically) as opposed to horizontally. The method that I used to work out the width of the 2nd monitor could then be used to work out the height of the 2nd monitor because the virtual screen would be then 1680x1950, and I could work out the 2nd monitor's height = 1950-1050 = 900. Thats also clumsy though, and I don't know how to change horizontal to vertical in VBA.

Any ideas?

I have made an excel spreadsheet. The area with data exactly fills my screen.
When I send it to other colleagues, it perfectly fills their screen too. But for some colleagues the data area is either too big for their screen or takes up a small proportion of their screen based on their screen resolution. I would like the area with data to perfectly fill the screen of all people who will access the file.
Do you know how I can solve this?
I am thinking that I would do an automatic event so that I can change the settings upon opening the file. I have Excel VBA programming for Dummies which explains about automatic events but I have no idea what sort of subprocedure to use.
Hoping you can help!

I'm creating a spreadsheet for work with several different VBA button (checkboxes, combobuttons, etc.). All of these buttons are placed so their position is far enough away from another field, right next to another field, etc (based on my screen resolution 1024x768). When I try to open the spreadsheet in a different resolution, everything is out of place (too close, too far, overlaps something else, etc.). Is there anything I can do with the code to prevent this, regardless of what screen resolution is used? Thanks for your help, Chris.


I am trying to use vba to automatically create charts. But, I can't get the chart shapes such as plot area and legend sized or moved properly that is uniform accross all different screen sizes/resolution. For example, if I were to set the sizes according to a 1280x1048 screen, running the same code on a 1680x1280 screen would totally distort would keep the plot size small and only adjust the chart size. So the actual chart size gets adjusted, but the shapes inside it do not do not.

I am using Excel 2003 and the code I am using to size is the following:

Selection.Height = 210 
Selection.Left = 15 
Selection.Top = 18 
Selection.Width = 335 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any advice would be appreciated.


As screen resolutions get 'bigger' (e.g., 1920x1080 vs. 800x600), the myriad of user forms that I created in my VBA projects (when 800 x 600 was the standard) become harder to read. Is there any way to 'magnify' my forms so that everything grows (like increasing the magnification of a webpage), or will I have have to resize everything manually.

I have created a spreadssheet form on a worksheet on my lap top witha really high resolution. On a 800X600 screen it looks a mess. is it possible in vba to automatically resize without me having to redesign the form?

Good morning everyone,

Just a quick question here. This is somewhat related to a post that I had made on here some time ago relating to adding controls to a userform using VBA.

My question is this:

Is it possible to to code your form in such a way so that when activated, it will detect the users screen resolution and resize itself so that it is always say 80% of both the x and y axis? For example, is the user has their screen resolution set at 800X600, then the userform would resize itself to 640X480.

There is a second part to this query, but I will leave that for a little later. My thanks to anyone who is able offer any insight on this.

using this code::

Private Declare Function GetSystemMetrics _
Lib "user32" (ByVal nIndex As Long) As Long
Private Const SM_CXSCREEN = 0
Private Const SM_CYSCREEN = 1

Private Sub UserForm_Initialize()
    X = GetSystemMetrics(SM_CXSCREEN)
    Y = GetSystemMetrics(SM_CYSCREEN)
    Me.Top = 0
    Me.Left = 0
    Me.Width = Y
    Me.Height = X - 470 ' TO CLEAR TASKBAR
End Sub

This works really really well, userforms are sized to the screen perfectly. its jsut the objects held on the userforms dont. they stay in the same place in the screen (e.g. buttons centered in my VBA editor will stay int he same place in use and wont centre on the users screen)

Is there anyway to resixe and replace the objects on the userform??

I have a spreadsheet that is being used to display various pieces of information about clients. It's arranged one row per client, and 15 or so columns. The columns contain a mix of short text, numbers, and very long text with linebreaks in it.

I'm using a VBA userform to input the information, then just writing it to the worksheet. I'm struggling with formatting the cells with very long text in them. I am setting word wrap on all the cells, but sometimes there is so much text that the cell gets too tall and exceeds the max height for a cell, and the information is truncated (just visually, the data is still there).

You can fix it manually by slowly widening the cell width until it looks ok, or you can autoformat each column, then autofit the row to make everything look ok, but doing so risks making the columns too wide to print on a single page.

So, my question is, does anyone have a good routine for how to handle this? I understand all the issues of users using different screen resolutions, font sizes etc, so it seems hard to hardcode things in points or twips or whatever.

I was thinking something along the lines of finding the longest string of text in the cell (in cells that have linefeeds) and trying to turn that into an average character count then setting the width to maybe 60% of that. That way the column widths wouldn't get too crazy.

Anyone have any different ideas? If there's a window/form API or something like that I can tap into I would love to try that.


I have experienced a problem when adding ToggleButton, SpinButton and OptionButton controls on a Worksheet. The issue can be reproduced by

1) opening a blank sheet, opening the control toolbar and adding one each of ToggleButton, Spinbutton, and OptionButton.

2) Change resolution of the display (in my case from 1600x to 1024x), then click back and forth on the controls and a random cell. The control will either grow or shrink on each click.

I have tried to reset the control size via event trapping in VBA (set width and height of controls) - seemed initially to work, however the interior graphics of the controls continue to shrink.

Any help or advice would be greatly appreciated.

-- Peter


I am trying to use vba to automatically create charts. But, I can't get the chart shapes such as plot area and legend sized or moved properly that is uniform accross all different screen sizes/resolution. For example, if I were to set the sizes according to a 1280x1048 screen, running the same code on a 1680x1280 screen would totally distort would keep the plot size small and only adjust the chart size. So the actual chart size gets adjusted, but the shapes inside it do not do not.

I am using Excel 2003 and the code I am using to size is the following:

    Selection.Height = 210
    Selection.Left = 15
    Selection.Top = 18
    Selection.Width = 335
Any advice would be appreciated.


Hi Guys

Im designing a desktop background for my office, and want to roll it out once complete.

Rather than trying to email the image out and get people to do it manually, is there anything in vba i can do to assist in this? I know its asking a bit much, but ive been surprised in the past with how versatile vba is! (i.e mapping network drives)

I asked the IT guys to do it over the server, but they started ranting about screen resolution, priority of other jobs etc etc.

Thanks in advance for any help

I am looking for someone who can help me do the following....


From the developer:
> > Here is what is in the file:
> > Step 1 (Issues) - removed Approach and Maturity columns, added
> > column, updated colors and fonts to match new pages
> > Step 2 (Stakeholder Issues) - created the worksheet, updated the
Jump To
> > menu and Main form to go to this worksheet as step 2
> > Step 3 (Modes and Sectors) - created the worksheet, updated the
Jump To
> > menu and Main form to go this worksheet as step 3, updated Step 4's
> > button, enabled sorting if you click on the underlined headers
> > Position Map - I tried putting a listbox in each box to allow for
> > scrolling. This worked for display, except if you had the Main
> > displayed first, it would somehow overlow the listboxes unless you
> > clicked on Refresh. I haven't been able to track down what this
> > bug is and how to workaround it, so I returned it to the way it
> >
> > I think I included one or two help popups. I'll doublecheck
> > and see if I can send some more.
> >
> > What needs to be done:
> > The Prepare_to_publish and Import procedures need to be updated to
> > populate the new worksheets. The modRecordMaintenance needs to
> > the new worksheets. The new worksheets need to update the
> > worksheet. This is all of that data integrity stuff that wouldn't
> > necessary if this was a real database. The Goa/Issue headers on
> > Sectors and Modes sheet need to be VLookups instead of hardcoded.
> >
> Additional notes from the developer:
> The code modules are fairly well laid out, so I think a strong
> could take over and complete the remaining work. The named ranges
> also very logical and easy to get a grip on. The steps are just
> involved. For example, from the Modes&Sectors page, you need to
> 1)update any existing stakeholder records (Stakeholder worksheet)
> 2)insert new stakeholder record rows (stakeholder worksheet) if new
> were added then 3) update the list of stakeholders on the
> StakeholderIssues page. You also need to do error checking like
> removing empty rows (users might leave those in between data rows),
> generating an error if the stakeholder name is not entered, but a
> is assigned. For even more data security, I'd recommend doing things
> like limiting the scrollable area so users can't be adding stuff at
> random spots on the worksheets. So it's fairly detailed stuff you
> to do for the updates.
> I did some work, but the next developer will probably need to
> go back and redo some of that since I started on the update logic,
> didn't complete it. The logic is complex enough it'd be better if
> he/she had a fresh start on that.
> ************************************************
> Tasks from client as well as a clarification of Task 4b:
> Task 2: Change Issue's Column Label to Coke Prioritization Levels
> - Update column label and test
> Task 3: Enhance Position Map
> - Modify font so that names are easier to read, possibly larger font
> alternating colors?
> - Add scroll bar for large lists
> - Test printing and different screen resolutions for best fit
> Task 4:
> Overall these are more complex layouts.
> 4a: Combine steps 2 & 3
> - Create new form
> - Add code to auto-populate cells from keystrokes and correctly move
> next step
> - Send mock-up to client for review, make any requested
modifications, and
> test
> Task 4b: Update step 4
> I'm not sure I understand this task. It says "We want to move a
couple of
> buttons off the current Step 4 and create a new UI for them." Which
> are we moving off of step 4? The mock-up seems related to the
> tab. Is the idea to get all of the fields in the General Info tab to
> different screen? If that is the case, then would the user see it
> after the current step 4?
> To clarify:
> A) Today, on Step 4: Database, in addition to free form text fields
> info, there are 3 other fields to assign values to this stakeholder
> 1) Sectors, 2) Modes of Engagement, 3) Highest Priority.
> B) Task 4B, also designated by the General Info tab in the Conceptual
> Mock-ups excel file sent to you, is to create separate UI for the
user to
> designate this particular information. We figure it will look alot
> UI you create for Steps 2&3, just to give the user a consistant UI to
> with).
> The reason we are making this a separte UI v.s. just having you add
it to
> Steps 2&3 UI is bcs my product mgr tells me theswe topics need to be
> on separately for some reason.
> The user would see this 2nd UI after the 1st UI you are creating -
> 2&3


Was hoping someone could help me with a problem that has me stuck. I am using excel/VBA to build a set of charts. Each chart is an XYScatter with data from each series displayed horizontally. Each series has multiple points, each with a different marker. Some markers are the default marker shapes which I color and size with VBA no problem. However, for some of the data points, I build a graphic object (a rectangle overlaid with a diamond of different color) and paste that in as the marker for that data point. This works well except when I go to lower screen resolutions.

In that case, the graphics are cropped and stretched in a strange way that appears to be related to the display resolution of the screen. At the higher resolutions (e.g. 1280x1024 and higher) everything looks right, but when res is dropped to 800x600 or 1024 x 780, the pasted markers are cropped/stretched. I have tried everything I can think of to fix this problem with no luck. I converted the graphics to pictures (tried all excel options), changed marker sizes, but no luck.

It appears to be a problem with pasting enhanced metafiles. If I build a graphic while in the reduced resolution, copy it and then paste special as an enhanced metafile, the pasted graphic is messed up in the way I am talking about. And it appears that the only way to paste in a graphic is using paste (which maybe defaults to enhanced metafile?) - I can't seem to use paste special for markers.

Does anyone have any ideas on what the problem is here and how I could solve it?

I am plotting an XY scatter chart on its own worksheet.

The worksheet will be used by many users with different monitors and screen resolutions.

In order to keep the chart from getting distorted by different aspect ratio monitors, I would like to be able to adjust the plot size to accomodate the different screen aspect ratios and to scale the % size to the largest to fill the screen

Does anyone have a suggestion on the vba code to accomplish this?

Hi, this is my first post to this forum but I've been really helped by the tips I've read from others in the past. Unfortunately these have taken me into areas I'm unfamiliar with, and so I'd really appreciate some help.

Having learnt how to use Forms to give the user the ability to select an item from a List Box, I got frustrated because I couldn't change the default font size (I'm designing a large file that is to be used on several machines with different screen resolutions, and shrinking the sheet magnification down makes it very difficult to select an item).

So I discovered the Control Toolbox and painstakingly swapped all my Form toolbar List Boxes over to Control toolbar List Boxes.

Two major problems resulted however. Firstly, I can't find any way to change the colour of individual items in the list. In one of my forms I have over a hundred items in the list, just one of which must be selected by the user, and I'd like individual items in the list to be one of a few different colours to make them easier to identify. Can this not be done? If not, how else can you suggest I could make certain items in the list stand out?

Most annoyingly, every time I move the mouse scroll wheel in a worksheet which contains a Toolbox-generated Form (usually just accidentally, it's easily done), Excel promptly crashes. It's fine if I use the scrollbar in worksheets that don't contain forms, or that contain forms that I created using the Forms toolbar however.

Apparently I'm not the first to find this, but is it a known bug in Excel (I found the behaviour described at ) I've taken mouse graphics acceleration or whatever it's called right off in case it was causing the problem, but without success.

I'd really appreciate suggestions for overcoming these problems - I thought the Toolbox type of forms were meant to be what you should use for user selections, and that the Forms toolbar was just a legacy. But if I can't get Control toolbox forms to work without crashing, what is the point of them, I'd be better off just sticking to using Forms, except they don't work properly except at 100% magnification.

Incidentally, I've no idea how to go about programming in VBA, so I hope you don't mind going easy with me if you tell me I need to start playing with Visual Basic codes, I'm a medical doctor not a computer programmer! For the record I'm running Excel 2003 as part of Office 2003 Professional on a Windows XP Professional Lenovo Thinkpad T60. Thanks in advance!

Hi to all,

I have run recently into problems while writing VBA code in Excel used for generating of Powerpoint presentation.

I have one quite huge Excel workbook and I need on monthly basis create reports in PPT. I decided to copy pictures of charts/tables from excel rather than updating links.

Everything seems to be fine but one issue - I have several charts which get cropped after the CopyPicture/Paste. It SEEMS to be some limitation of Excel/Powerpoint as the max image size is very similar to the screen resolution of my computer?

The snip of the code:
Worksheets("Test").Shapes("Chart 1").CopyPicture Appearance:=xlScreen,Format:=xlPicture

Set NewS = PPSlides.Shapes.PasteSpecial Link:=False,DataType:=wdPasteMetafilePicture,DisplayAsIcon:=False
' or even
' Set NewS = PPSlides.Shapes.Paste
' doesn't work as expected
Does anyone have any idea how to go around this? I really need this huge charts (they are actually long) as I have some scrolling functionality around it to allow viewers to scroll for information they are interested in...

I'm at the end of my ideas - any help is appreciated!

Thank you!

We developed a tool for a contact centre in Ecxel 2003 (VBA). All the desktops have different screen resolution. We force change the resolution of the desktops for the tool to fit all screens. But we have a problem with some of the desktops. When you open the form and click on the combobox to select the value it appears as if the combobox drops down behind the other controls. All you see is a small line for the dropdown part. You can still click on the line but you are not sure what you are clicking on because the line appearing is too thin to show any values. What can we do to solve the problem? They all have service pack2.

No luck finding an answer? You could always try Google.