[Solved] VBA : Viewing problem

  • I have a Userform that opens up when the user opens the workbook. everything looks fine on my computer and a few others, but one computer seems to zoom in so it cuts off alot of the userform. I checked the resolution on that computer and its same as mine.
    I have the userform auto maximize upon opening, but get the same problem. And it is only on this one computer. Any ideas would be much appreciated. (he is using 2000)


  • You could try something along the lines of;

    Private Sub UserForm_Activate()
    Me.Width = (ActiveWindow.Width) / 2
    Me.Height = (ActiveWindow.Height) / 2
    End Sub

  • I appreciate your reply, but that only decreases the size of the window, but keeps the User form the same size, instead of zooming out the controls to fit the window. I hope that that makes sense. Basically its acting as if the computer is at a lower resolution (like 800 x 600) but its only doing it in Excel.
    His Windows is at the same resolution as mine, just the Excel User form looks much larger, and shrinking the window keeps the controls at the same size, so more of the userform gets cut off.

  • The Userform. Basically I have the Userform so it is full screen, 1024 by 768 resolution. When I open it on hi computer it's as if it is displaying it at 800 by 600. I checked his resolution on his computer and it is 1024 by 768.
    When I use the zoom command, it zooms out the backround, but the controls are still big and bleed off the screen.

  • Hi Brister:

    First of all, a few questions:
    1) Are the monitors the same size? (although this shouldn't matter if the resolution is set the same).
    2) Are the computers running the same video hardware?
    3) On the culprit machine, are there any custom views set in Excel?

    Seems to me I had a similar problem quite some time ago, and it took me a lot of monkeying around to figure it out. I seem to recall that setting the application to Full Screen had something to do with it. It may make a difference when you set Full Screen; i.e. before the workbook opens or after the workbook opens. Play around with it.

    Also, you may want to remove the auto maximize upon opening from your workbook open event and see what happens.

    Good luck, and keep us posted.


  • I appreciate your feedbacks. Here's what I ended up doing:

    Private Sub UserForm_initialize()
    If DisplayVideoResolution = "1024 x 768" Then GoTo Res1:

    Call initialize
    GoTo addins:

    With Application
    Top = .Top
    Left = .Left
    Height = .Height
    Width = .Width
    End With

    Call Addin

    End Sub

    Sub Initialize()
    CH = AdTrack.Calendar1.Height
    CW = AdTrack.Calendar1.Width

    If DisplayVideoResolution = "800 x 600" Then GoTo Res2:
    If DisplayVideoResolution = "640 x 480" Then GoTo Res3:
    GoTo Theend:

    Zoom = 80
    AdTrack.Zoom = Zoom
    AdTrack.Calendar1.Width = (CW * 0.8)
    AdTrack.Calendar1.Height = (CH * 0.8)
    GoTo Theend:


    Zoom = 50
    AdTrack.Zoom = Zoom
    AdTrack.Calendar1.Width = (CW * 0.5)
    AdTrack.Calendar1.Height = (CH * 0.5)
    GoTo Theend:

    End Sub

    A little rough, but it worked. I had to zoom the Calendar separatly because it would not zoom with the subform. I changed resolutions on my system to get the right zoom %.
    Tried it on the other guys computer, and it did not work, but may be because he has an excel bug or something. His system says he is at 1024 x 768 so it will not zoom, and his monitor is the same as mine. I think he may have a bug or something, because it works on everyone elses computers.
    Oh well, thanks for your suggestions.

  • Hey Derk!

    Yes, I changed his resolution to 800 by 600 and ran it. Came out the same size as it did at 1024 by 768.

    The wierd thing is his computer looks like the corresponding resolution, it just seems that Excel is viewing at 800 by 600 resolution but does not change the actual system resolution. Could it possibly be a Excel virus?

  • Hi Brister:

    Your solution was of interest, as I had not come across the method or property "DisplayVideoResolution....."

    I am running W2K with Excel 2000, and my system does not recognize this, it ignores it. You suggested the culprit system was running 2000. That computer may be ignoring the statement as well.

    If you trace your code back to the origin, you will probably find that Res1:, Res2:, & Res3: never get executed on the culprit computer, no matter whatt the screen resolution is. You may want to test this by inserting some message boxes in each of these three scenarios.

    Let me know how it goes.



  • I just checked another computer that is also running W2K with Excel 2k, and its seems to work fine on his. I changed the resolution to 800 by 600, and 640 by 480 and it zoomed properly, so the code does work in Excel 2k. Of course I learned that at 640 by 480 i have the zoom set too high and the text on the controls distort a lot at that resolution.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!