Solved

How can I find out the screensize?

Posted on 2004-09-17
7
271 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 50

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 50

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

805 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