Change Excel Windows sizes to half the screen

Question

I have 4 Excel Windows:

• Microsoft Visual Basic for Applications - PERSONAL.XLSB [break]

• Workbook1

• Workbook2

• Workbook3

I want to Change the sizes of my windows in that way:

• VBA will be: Height = 100% of my computer height, Width = 50% half of my computer width and position of the window should be on the right side.

• Workbooks 1-3 will be: Height = 100% of my computer height, Width = 50% half of my computer width and position of the window should be on the left side.

**I guess for access to Microsoft Visual Basic for Applications - PERSONAL.XLSB [break] I need a reference to windows system library, but i believe it should be possible

Any ideas please?

Answer 1

May be this will help you.

Sub SetWindowSize1()
    Application.WindowState = xlNormal
    Application.Top = 25
    Application.Left = 25
    Application.Width = 300
    Application.Height = 200
End Sub

And to set the window width and height as per the screen size, here you can see the code: https://www.ozgrid.com/forum/index.php?thread/142634-vba-to-automatically-resize-user-form-depending-on-screen-resolution/

Code to loop through VBA windows:

Sub ListWindows()
Dim wn As Excel.Window
For Each wn In Application.Windows
    MsgBox wn.Caption
Next wn
End Sub

Answer 2

You can try below code:

Sub ChangeWindowSize()
    ' get size of a screen
    Application.WindowState = xlMaximized
    windWidth = Application.width
    windHeight = Application.height
    ' set size and location of a main workbook
    Application.WindowState = xlNormal
    Application.Top = 0
    Application.Left = 0
    Application.width = windWidth / 2
    Application.height = windHeight

    Call OpenOtherWorkBookAndSetSize(windWidth, windHeight)
End Sub

Sub OpenOtherWorkBookAndSetSize(windWidth As Variant, windHeight As Variant)
    Dim appExcel As Application
    Dim objWorkbook As Workbook
    Dim i As Integer

    'create new excel application object
    Set appExcel = New Application
    'open other workbook
    Set objWorkbook = appExcel.Workbooks.Open("path to the file")
    ' just example setting
    appExcel.WindowState = xlNormal
    appExcel.Top = 0
    appExcel.Left = windWidth / 2
    appExcel.width = windWidth / 2
    appExcel.height = windHeight
    appExcel.Visible = True
End Sub

It will split your screen into halves :) Now you can modify further this code :)

Example result: