Excel Zoom Setting and Userform Placement Setting

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
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):
Code:
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?
Code:
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

Well-known Member
Joined
Oct 30, 2011
Messages
3,754
Hey

Code:
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

Board Regular
Joined
May 25, 2017
Messages
76
Worf,

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

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,754
You are welcome.

Do you need help with the user form positioning?
 

Forum statistics

Threads
1,077,731
Messages
5,335,893
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top