Code to control window characteristics
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Code to control window characteristics

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is it possible to add code to a WB or individual Worksheets so that every time a named sheet is viewed, it assumes some predetermined condition(s) such as Normal or Maximized, exact size and location of window in Normal view, etc., regardless of the prevailing conditions when it was last viewed and regardless of the current state of Excel? For example, if two files are open and File1 is currently maximized in the Excel window, when switching to File2 I want to see it in Normal view, not maximized, and with 2 windows (views of the same file) open (which is the way it would last have been viewed/saved). Then, switching to File1, I want it to appear Maximized.

    Now for the tough part. Assume File2 is open with 2 windows (views of the same file) side by side which together take up Excel's available screen space. Will code allow you to set window size/location based on the user's screen resolution? Some users have 14" monitors while others have 18" so some are running 800x600 while others are using 1024x768. Ideally, the code would size the two views of the same file proportionately to take up Excel's available screen space, i.e. View1 is 70% of available width and View2 is 30%. Impossible?

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Play around with code like the following:

    Application.DisplayFullScreen = True
    Application.DisplayFullScreen = False
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView
    Windows.Arrange ArrangeStyle:=xlVertical
    Windows.Arrange ArrangeStyle:=xlTiled

    There are more options that I haven't listed.

    The following code will return your screen resolution:

    Option Explicit
    Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

    'Constants for GetSystemMetrics

    Const SM_CXSCREEN = 0 ' Width of screen
    Const SM_CYSCREEN = 1 ' Height of screen


    Sub Get_System_Metrics()

    Dim XVal As Long, YVal As Long
    YVal = GetSystemMetrics(SM_CYSCREEN)
    XVal = GetSystemMetrics(SM_CXSCREEN)
    MsgBox "Your Screen Resolution is " & XVal & " by " & YVal

    End Sub
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com