MENU Problem

[TAM]

I have designed a user front-end for an Excel workbook but the problem I
have is finding a way of adjusting the object locations when the screen
resolution is different from the design machine. At worst can vba read what
the host resolution is in which case I can use a different worksheet

Many thanks

TAM

 

[Tom Ogilvy]

Here a a couple of approaches:

http://support.microsoft.com/?id=148395
ACC: How to Determine the Current Screen Resolution (95/97)


'*****************************************************************
' DECLARATIONS SECTION
'*****************************************************************
Option Explicit
Type RECT
x1 As Long
y1 As Long
x2 As Long
y2 As Long
End Type
' NOTE: The following declare statements are case sensitive.
Declare Function GetDesktopWindow Lib "User32" () As Long
Declare Function GetWindowRect Lib "User32" _
(ByVal hWnd As Long, rectangle As RECT) As Long
'*****************************************************************
' FUNCTION: GetScreenResolution() ' ' PURPOSE:
' To determine the current screen size or resolution. '
' RETURN:
' The current screen resolution. Typically one of the following:
' 640 x 480 ' 800 x 600 ' 1024 x 768 '
'*****************************************************************
Function GetScreenResolution() As String
Dim R As RECT
Dim hWnd As Long
Dim RetVal As Long
hWnd = GetDesktopWindow()
RetVal = GetWindowRect(hWnd, R)
GetScreenResolution = (R.x2 - R.x1) & "x" & (R.y2 - R.y1)
End Function


==============
Posted by Laurent Longre, Programming 06/10/99

Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hdc As Long, _
ByVal nIndex As Long) As Long
Declare Function GetDC Lib "User32" (ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib "User32" (ByVal hWnd As Long, _
ByVal hdc As Long) As Long

Sub Test()
Dim DC As Long
DC = GetDC(0)
MsgBox "Resolution : " & GetDeviceCaps(DC, 8) _
& " * " & GetDeviceCaps(DC, 10) & " pixels"
ReleaseDC 0, DC
End Sub

==================
Another by Laurent: ( color depth)

Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long

Declare Function GetDC Lib "User32" (ByVal hWnd As Long) As Long

Sub Test()
MsgBox "Color depth = " & GetDeviceCaps(GetDC(0), 12) & " bits"
End Sub

=======

If you want a certain area of the screen to be visible regardless of
resolution:

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Selection
Range("A1:A24").Select
ActiveWindow.Zoom = True
rng.Select
End Sub

http://www.cpearson.com/excel/zoom.htm


Note that either the number of rows will dictate the number of columns or
vice versa.

 

[tony moody]

Thanks very much for your reply, the SetRng option works a treat