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