Excel Zoom Setting and Userform Placement Setting

[NickRed18]

Hey Everyone,

I have been working on a VBA project for about the last 2 weeks, and I am confident enough in it to call it done (yay!).
There is one thing, however, that is tripping me up... The macro will be run on a couple different computers around the office - which means it will be run on systems with different resolutions. One of my sheets, in the workbook, is a Display: I'm using the range "A1:J32" to show the user the status of different parts - basically a precisely sized table of colors/values.

Whenever the workbook is used on a different sized computer screen, the workbook does not size "proportionally" which requires the user to zoom to the right percentage.

This in turn also messes with my userform ("NewItemInterface") placement somehow. I am using the following code to place my userform (as you can see, my displacements of 323 and 1103 were found through trial and error to result in the exact location I want):

NewItemInterface.Top = Application.Top + 323
NewItemInterface.Left = Application.Left + 1103

Is there a way to set the zoom to a certain range of cells so that the display will look the same everytime, and set the userform to an exact spot relative to the screen?

ActiveWindow.Zoom to Range("A1:J32")
NewItemInterface.Top = 60% of the screen's height from the top
NewItemInterface.Left = 90% of the screen's width from the left

 

[Worf]

Hey

Sub AdjustZoom()
Dim a, zstep%, c%
zstep = 1
a = Split(ActiveWindow.VisibleRange.Address, ":")
a = Split(a(1), "$")
If Range(a(1) & "1").Column < 10 Then zstep = -1        ' decrease zoom
c = 0
Do Until Range(a(1) & "1").Column = 10 Or c > 20        ' adjust to column J(=10)
    ActiveWindow.Zoom = ActiveWindow.Zoom + zstep       ' increase or decrease
    a = Split(ActiveWindow.VisibleRange.Address, ":")
    a = Split(a(1), "$")
    c = c + 1                                           ' safety
    [a1].Activate
Loop
c = 0
If a(2) < 32 Then                                       ' adjust to row 32 which is not visible
    Do Until a(2) >= 32 Or c > 30
        ActiveWindow.Zoom = ActiveWindow.Zoom - 1       ' decrease
        a = Split(ActiveWindow.VisibleRange.Address, ":")
        a = Split(a(1), "$")
        c = c + 1
        [a1].Activate
    Loop
End If
End Sub

 

[NickRed18]

Worf,

Thanks for the bit of code! I would never have thought to iterate with zoom.

 

[Worf]

You are welcome.

Do you need help with the user form positioning?

 

[NickRed18]

I found an old post with exactly what I needed. Thanks though.