Center any Application From Excel-VBForums
Results 1 to 2 of 2

Thread: Center any Application From Excel

  1. #1

    Thread Starter
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Center any Application From Excel

    This piece of code 'Centers' an open Application Window from Excel. What I mean is that it moves the Application Window and makes it 'Center Screen' with relative to the Excel Workbook and not relative to desktop.

    The following code takes an example of Notepad. To test this, open notepad and place it anywhere on the screen.

    Create a new workbook and paste this code in a module and then run the Sub CenterApp()

    Sid

    Code Used

    vb Code:
    1. '~~> Code Created By Siddharth Rout on 5th March 2011
    2. '~~> You may freely use it in your Application however
    3. '~~> Please do not delete these 3 lines from the code
    4.  
    5. Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    6. (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    7.  
    8. Public Declare Function SetWindowPos Lib "user32" _
    9. (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, _
    10. ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
    11.  
    12. Private Type RECT
    13.     Left As Long
    14.     Top As Long
    15.     Right As Long
    16.     Bottom As Long
    17. End Type
    18.  
    19. Public Declare Function GetClientRect Lib "user32" (ByVal hWnd As Long, _
    20. lpRect As RECT) As Long
    21.  
    22. Sub CenterApp()
    23.     Dim Ret As Long
    24.    
    25.     '~~> Caption of Application to be centered
    26.     Dim strCaption As String
    27.    
    28.     '~~> For Storing Width and Height of Application to be centered
    29.     Dim CAppWidth As Long, CAppHeight As Long
    30.    
    31.     '~~> For Storing Width and Height of Excel Workbook
    32.     Dim ExcelAppWidth As Long, ExcelAppHeight As Long
    33.    
    34.     '~~> This is required to get the Height and Width of
    35.     '~~> Application to be centered and of Excel Workbook
    36.     Dim rc As RECT
    37.    
    38.     '~~> For new position of the Application to be centered
    39.     Dim NewLeft As Long, NewTop As Long
    40.    
    41.     '~~> Testing it with Notepad. Replace this with the Application's caption
    42.     strCaption = "Untitled - Notepad"
    43.    
    44.     '~~> Get the handle of the Excel - Book1. Please change the caption
    45.     '~~> accordingly. This is one classic case where WYSIWYG doesn't apply
    46.     '~~> Window will show "Book1 - Microsoft Excel" but it is the opposite
    47.     Ret = FindWindow(vbNullString, "Microsoft Excel - Book1")
    48.    
    49.     '~~> Get the width and height of "Microsoft Excel - Book1"
    50.     GetClientRect Ret, rc
    51.     ExcelAppWidth = rc.Right - rc.Left
    52.     ExcelAppHeight = rc.Bottom - rc.Top
    53.    
    54.     '~~> Get the width and height of Application to be centered
    55.     Ret = FindWindow(vbNullString, strCaption)
    56.     GetClientRect Ret, rc
    57.     CAppWidth = rc.Right - rc.Left
    58.     CAppHeight = rc.Bottom - rc.Top
    59.    
    60.     '~~> Calculate the new position of the Application to be centered
    61.     NewLeft = (ExcelAppWidth / 2) - (CAppWidth / 2) + ActiveWindow.Left
    62.     NewTop = (ExcelAppHeight / 2) - (CAppHeight / 2) + ActiveWindow.Top
    63.    
    64.     '~~> Set the position
    65.     SetWindowPos Ret, 0, NewLeft, NewTop, CAppWidth, CAppHeight, 0
    66. End Sub
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  2. #2

    Thread Starter
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Center any Application From Excel

    Snapshot: Before and After.
    Attached Images Attached Images   
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.