Fitting Workbook to Different Screen

[Pacman52]

Hi all

Does anyone know how I would 'fit' a WB to show the same way it does on my monitor?

The WB is coded to open maximised and has some 'pop up' Userforms and some buttons on different sheets which are all positioned exactly where they should be on my monitor but when I transfer the WB to another smaller monitor the various 'elements' appear of centre.

I've read some possible solutions to this but they all seem to be for 32bit and I'm on 64bit. Also it may be easier if I point out the WB will only be used on 2 computers mine and my partners, both capturing different data so with that in mind is there a simple way to 'design' two versions, one for each monitor on my monitor if I can find out the resolution.

Thanks

Paul

 

[rlv01]

A simple way with userforms is to add code to the form's Activate event to center the form on whatever monitor.

    With Me                           ' Center Screen on whatever monitor Excel is running on
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)

        If .Height > Application.Height Then
            .ScrollHeight = .Height
            .Width = .Width * 1
            .Height = .Height * 0.8
            .Zoom = 100
            .Top = .Top + 60
            Me.KeepScrollBarsVisible = fmScrollBarsVertical
            Me.ScrollBars = fmScrollBarsVertical
        End If
    End With

 

[Pacman52]

Many thanks for the reply - As I needed to get this finished in the end I just re-designed the WB on the actual screen resolution of the monitor and made some minor adjustments via TeamViewer so all is ok now but that code will 100% be used on other projects so I'm really grateful for you referencing it.

Paul