screen size

[mahmed1]

Hi - Please advise if this is possible

If i know the screen size of my monitor - say my monitor is 20 x 11.3 (1920 x 1080 screen res) - is there a way i can have a formula in VBA that will give me the height and and width of the excel window if it was maximised?

The reason why i ask is that i have several of screen sizes a user can open this spreadsheet on - i want to change the zoom level depending on what size the spreadsheet was open on but i dont have access to those monitors to exactly what the height and width will be for those dimensions

Is there a way i can find out the height and width of the excel window maximised giving the screen size?

Thank You

 

[Haluk]

Try this;

Sub Test()
    Application.WindowState = xlMaximized
    strMsg = "Excel is now maximized"
    appWidth = Application.Width
    appHeight = Application.Height
    MsgBox strMsg & vbCrLf & "width= " & appWidth & vbCrLf & "height= " & appHeight
End Sub

 

[mahmed1]

Hi

Tgis isn’t exactly what I want- i wat to be able to pass the dimensions of a screen size and then get what the size of the excel window would be if it was maximised

thanks

 

[Haluk]

I am not sure for what you are asking but, the following code may give you some details;

#If Win64 Then
    Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
'
Sub Test2()
    Dim objWMI As Object
    Dim resultsWMI As Object
    Dim objMonitor As Object
    Dim monitorWidth As Double
    Dim monitorHeight As Double
    Dim monitorDiagonal As Double

    Set objWMI = GetObject("winmgmts:\\.\root\WMI")
    Set resultsWMI = objWMI.ExecQuery("Select * From WmiMonitorBasicDisplayParams")

    For Each objMonitor In resultsWMI
        monitorWidth = objMonitor.MaxHorizontalImageSize
        monitorHeight = objMonitor.MaxVerticalImageSize

        strMsg1 = "-Physical dimensions of your monitor is: " & monitorWidth & " cm x " & monitorHeight & " cm"
        monitorDiagonal = Format(((monitorWidth ^ 2 + monitorHeight ^ 2) ^ 0.5) / 2.54, "0.00")
        strMsg1 = strMsg1 & " (" & monitorDiagonal & " inches)"
    Next
    
    strMsg2 = "-Your current resolution is: " & GetSystemMetrics(SM_CXSCREEN) & " pixel x " & GetSystemMetrics(SM_CYSCREEN) & " pixel"
    
    appWidth = Application.Width
    appHeight = Application.Height
    strMsg3 = "-Excel's current window is: " & appWidth & " pixel x " & appHeight & " pixel"
    
    MsgBox strMsg1 & vbCrLf & vbCrLf & strMsg2 & vbCrLf & vbCrLf & strMsg3, vbInformation
End Sub