Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

How can I find out the screensize?

Hi
I need to find out the computers screensize from my excel- document (excel2000), and from that decide the zoom- factor of the spreadsheet.
How can I do that?

Mats
0
hucklberry
Asked:
hucklberry
2 Solutions
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Here is one way to determine the screen resolution:

Option Explicit

Private Type RECT
    x1 As Long
    y1 As Long
    x2 As Long
    y2 As Long
End Type

Private Declare Function GetDesktopWindow Lib "User32" () As Long
Private Declare Function GetWindowRect Lib "User32" (ByVal hWnd As Long, rectangle As RECT) As Long

Private Sub foo()
    Dim screenRect As RECT
    Dim hWnd As Long
    Dim ret As Long
    Dim screenWidth As Long
    Dim screenHeight As Long
    Dim resolution As String
   
    hWnd = GetDesktopWindow()
    ret = GetWindowRect(hWnd, screenRect)
    screenWidth = screenRect.x2 - screenRect.x1
    screenHeight = screenRect.y2 - screenRect.y1
    resolution = screenWidth & "x" & screenHeight
    MsgBox resolution
End Sub
0
 
Erick37Commented:
Or...

Declare Function GetSystemMetrics Lib "User32" (ByVal nIndex As Long) As Long

Const SM_CXSCREEN As Long = 0
Const SM_CYSCREEN As Long = 1

Public Property Get ScreenHeight() As Long
   ' Return screen height in pixels.

   ScreenHeight = GetSystemMetrics(SM_CYSCREEN)
End Property

Public Property Get ScreenWidth() As Long
   ' Return screen width in pixels.
   
   ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Property

from:
Office VBA and the Windows API
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03082001.asp
0
 
David LeeCommented:
Here's another.  This uses WMI (Windows Management Instrumentation) to get that info.  If there are multiple video cards in the system, which is unusual, then this query would return multiple video records.  The Function would then need to be modified.

Private Function GetScreenResolution() As String
    Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
    Set colItems = objWMIService.ExecQuery("Select * from Win32_VideoConfiguration",,48)
    objItem.HorizontalResolution & "x" & objItem.VerticalResolution
End Function
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ryan ChongCommented:
or look for the Screen.Width / Screen.TwipsPerPixelX and Screen.Height / Screen.TwipsPerPixelY:

Public Type POINTAPI
   X As Long
   Y As Long
End Type

Public Sub GetResolution(ByRef res As POINTAPI)
    res.x = Screen.Width / Screen.TwipsPerPixelX
    res.y = Screen.Height / Screen.TwipsPerPixelY
End Sub

then

Dim myRes As POINTAPI
GetResolution myRes

msgbox myRes.x & " x " & myRes.y
0
 
Erick37Commented:
I don't believe the Screen object is available in VBA.
0
 
Ryan ChongCommented:
ah... Erick you're right, didn't read question clearly..

Disregard my comment and try other experts examples. cheers
0
 
hucklberryAuthor Commented:
Thanks for your help!
The solutions from IdleMind and Erik37 both worked fine. Hope that it's ok to split the points between you (IdleMind was first,thats why I gave him/her 100p).

BlueDevilFan's solution didn't work for me for some reason.

mats
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now