Solved

How can I find out the screensize?

Posted on 2004-09-17
7
270 Views
Last Modified: 2010-05-02
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
Comment
Question by:hucklberry
7 Comments
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 100 total points
ID: 12089319
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
 
LVL 32

Assisted Solution

by:Erick37
Erick37 earned 75 total points
ID: 12089467
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
 
LVL 76

Expert Comment

by:David Lee
ID: 12089503
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 12089654
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
 
LVL 32

Expert Comment

by:Erick37
ID: 12089747
I don't believe the Screen object is available in VBA.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 12089803
ah... Erick you're right, didn't read question clearly..

Disregard my comment and try other experts examples. cheers
0
 

Author Comment

by:hucklberry
ID: 12090808
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now