Moving form to a cell location, App Size, Worksheet Size, Usable Area

[Jeffrey Mahoney]

I thought I would share some code to move a form to a specific location based on a cell. I found some VBA code from many different postings on MR Excel and other sites, but I wasn't quite happy with the results. So I finally tried a combination of things.

One of the annoying things is if the bottom of the Excel Window (on my machine) was near the bottom of the screen, I got a different result from when the bottom wasn't near. So I created a fudge factor. I guessed at the Statusbar height and then played with some numbers until the form was moved to the same vertical location each time. If you want to let me know if that works for you or not, that would be great.

With this method, I subtracted the usable height + statusbar height from the total height to get the top part of the window where the Cells actually begin.

Enjoy

Jeff

Private Sub UserForm_Initialize()
  Dim AppLeft As Single      'Application
  Dim AppTop As Single
  Dim AppWid As Single
  Dim AppHt As Single
  
  Dim FrmTop As Single    'Form
  Dim FrmLeft As Single
  Dim FrmWid As Single
  Dim FrmHt As Single
  
  Dim CellTop As Single   'Cell
  Dim CellLeft As Single
  Dim CellHt As Single
  Dim CellWid As Single
  
  Dim wsWid As Single     'Worksheet Useable Area
  Dim wsHt As Single
  Dim wsTop As Single
  Dim SBHt As Single      'Statusbar Height
  
  Dim WinState As Long    'Maximized or not
  Dim ScrnHt As Single    'Screen height from App Ht while maximized
  
  
  '...



  'Public variable  
  Set InsertRng = Sheets("Rollup").Range("DateSelection")  'Cell to move the form to
  
 'Get Screen Bottom
  Application.ScreenUpdating = False
  WinState = Application.WindowState    'Save windowstate
  If WinState <> xlMaximized Then Application.WindowState = xlMaximized   'Maximize
  ScrnHt = Application.Height           'Get Screen Height
  Application.WindowState = WinState    'Return to previous window state
  Application.ScreenUpdating = True
  
  'Excel Window Measurements
  AppLeft = Application.Left
  AppTop = Application.Top
  AppWid = Application.Width
  AppHt = Application.Height
  
  'Cell Measurements
  CellTop = InsertRng.Top
  CellLeft = InsertRng.Left
  CellHt = InsertRng.Height
  CellWid = InsertRng.Width
  
  'Area within the formula bar and bottom (not including statusbar)
  wsWid = ActiveWindow.UsableWidth
  wsHt = ActiveWindow.UsableHeight
  
  'Fudge factor for height of statusbar.
  'There is a difference when Excel Window bottom is near bottom of screen or not
  'I'm not sure it's the statusbar, but it works
  If Abs(ScrnHt - AppHt) < 6 Then
    SBHt = 18
  Else
    SBHt = 13
  End If
  wsTop = AppTop + AppHt - wsHt - SBHt
  
  CalendarFrm.Move AppLeft + CellLeft + (CellWid * 0.4), wsTop + CellTop
  
 '...
  
  
  Application.EnableEvents = True
End Sub

 

[JLGWhiz]

Just a passing comment. I don't think there is a "One size fits all" solution for placing forms or shapes on a worksheet. The same calculations on one system may yield different results on another. But the methodology of using the UsableHeight and UsableWidth along with other object height and width to calculate the positioning is noteworthy. I have used it many times on personal projects.