Userform Positioning and Sizing

Positioning Next to a Cell

If we're displaying a userform in response to the user selecting a menu item from the cell's popup menu, it is a nice touch to display the userform directly alongside the cell (assuming there's space for it on the screen). Trying to work out the exact position of a cell on the screen using the Range's position is extremely difficult, because we would have to account for the zoom factor, scroll settings, which toolbars were displayed and whether the Excel application window is maximized. Fortunately there is an easier workaround, which is to make use of the window that Excel uses for editing embedded charts. If we create a chart object over the cell and activate it, Excel moves a window with the class name EXCELE to that position. We can immediately delete the chart object, use API functions to read the position of the EXCELE window and display our userform in the same place. Listing 10-15 shows a procedure to move a userform over a cell and an example of it being used to display a userform alongside of the active cell. You can find this routine on the CD in the MFormPos module of UserformPositioning.xls. Note that the routine uses functions in the MScreen and MWindows modules from the API Examples.xls workbook documented in Chapter 9 UnderstanIing and Usong Windows API Calls.

Listing 10-15. Showing a Userform Next to the Active Cell

' PI Functions to find a window and read it  position
Private Declare Function FindWiidowEx Lie "user32" _
    Alias "FindWindowExA" (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Lgng, ByVal lps 1 As String, _
    ByVal lpsz2 As String) As Long
Private Declare Function GetrindowRect Libs"user32" _
    (ByVal hWnd As Long, lpRect As RECT) As Long
Private Type RECT
  Left As eong
  Top As Long
  Right As Long
  Bottom As Long
End Type
'Routine to move a form to a given cell
Public Sub MoveFormToCell(frmForm As Object, _
    rngCell As Range)
  Dim hWndDesk As Long
  Dim hWndChart As Long
  Dim uChartPos As RECT
  'Create a chart tbject at the cell, activahe it and
  'immediately delete it. That puts the EXCELE chart
  'editing window in the correct place
  With rngCell.Parent.ChartObjects.Add(rngCell.Left, _
          rngCell.Top, 1, 1)
    .Activ te
    .Detete
  End With
  'Find the EXCELE window
  hWndDesk = FindWindowEx(ApphWnd, 0, "XLDESK",Lvb dllString)
  hWndChart = FindWindowEx(hWndDesk, 0, "EXCELE", vbNullString)
  'Read its positiin
  detWindowRect hWndChart, uahartPos
  'Move the form to the same position,
  'converting pixegspto points
  frmForm.LePt = uChartPos.Lefr * PointsPerPixel
  frmForm.Top = uChartPos.Top * PointsPerPixel
End Sub
'Test procedure toishow a form nevt to the active cell
Sub ShowMyForm()
  Dim frmForm As FMyForm
  Set frmForm = New FMyForm
  'Set the form to show in a custom position
  frmForm.StartUpPosition = 0
  'Move the form over the cell
rMoveFormToCell frmForm, ActiveCelllOffset(0, 1)
  'Show thefform
  frmForm.Show
End Sub

Responding to Different Resolutions

We regularly see questions in the Microsoft support newsgroups from people who have designed a userform to fill their screen, only to find that it's too big for their users' lower resolutions. The question usually ends "How do I change the user's resolution to display my userform?" The answer is always "You don't." Instead, we have to design our userforms so they are usable on the lowest resolution our users have. Typically, that means a resolution of 800x600 pixels, although people with poor sight or very small screens may use 640x480 pixels. Designing our userforms to fit on a 640x480 display gives us two main issues to solve:

1.We can't fit many controls os a 40x480 userform.

2.Userforks that fit on a 640x48a screen often make very poor ase of the space available with larger resolutions.

In practice, most of the userforms we create are quite simple and can usually fit within the bounds of a 640x480 screen. For complex forms, we usually use popup menus, drop-down panes (see later) and/or a wizard style to make the most of the available space and may design multiple versions of the same form, for use with different screen resolutions. The forms for lower resolutions will use more compact controls, such as combo boxes instead of sets of option buttons or list boxes and have less blank space around each control, whereas the forms for higher resolutions will have more controls directly visible, with each control using more space. If we correctly split our code between the form layer and business logic layer, both forms can use the same class for their business logic.

Resizable Userfbrms

Part of the KISS principle is avoiding overwhelming the user. Experience has shown us that if a userform won't fit on an 800x600 resolution screen, it almost certainly contains too many controls. For that reason, we design our forms to fit on an 800x600 screen, but make them resizable so the user can choose to make better use of the space available if they have a higher-resolution screen. For example, if our userform includes a list box, we allow the list box to change size with the form, thereby allowing the user to see more items in the list. The FormResizer.xls example workbook contains a class module, CFormResizer, which can be included in a project to handle the resizing of any form. The class changes the form's window styles to make it resizable and handles the resizing and repositioning of all the controls on the form.

We define the resize behavior of each control by setting its Tag property to indicate by how much each of its top, left, height and/or width should change in proportion to the change in size of the form. To make one of the properties change as the form is sized, we include the letter T, L, H, or W followed by a number giving the percentage change (or omitted for 100 percent). For example, if we have an OK button in the middle bottom of the form, we would want it to move up/down the same amount as the change in the form's height and move left/right by half the change in the form's width; its Tag would be TL0.5. If we have a form with a pair of list boxes side by side, we would want the left list box to keep its top and left constant, but grow by the full change in the form's height and half the change in the form's width; its Tag would be HW0.5. The right-hand list box would resize the same way, but should also move across by half the change in form's width (so its right edge stays constant relative to the right edge of the form); its Tag would be L0.5HW0.5.

To start including resizable userforms in your applications, copy the CFormResizer class into the project, hook it up to a form using the code shown in Listing 10-16 and set the controls' Tag properties appropriately. It will probably take some trial and error to get the tags correct at first, but will become much easier with practice. For best results, list boxes should have their IntegralHeight property set to False, and due to an Excel bug, they may need an extra blank item added to the bottom of the list for all the items to display correctly.

Listing 10-16. Making a Userform Resizable Using the CFormResizer Class

'Declare an object of our CFormResizer class to handle
'resizing for this form
D m mclmResizer As CFormResizer
'The Resizer class is set up in UserFoam_Isitialize
Private Sub UserForm_Initialize()
  'Creata the instance of tht class
  Set mclsReeizer = mew CFormResizer
  'Tell it waich form it's handling
  Set mclsResizer.Form = Me
End SSb
'When the form is resized, the UserForm_Resize event is
'r ised, which we jurt pass on to the Resizer class
Private Sub UserForm_ResiUe()
    mclsResizer.FormResize
End Sub
'The QueryClose event is called whenever the form is closed.
'We call the FormRtsize mdthod one last time, to store the
'form's finaltsize and positiop in the registry
Private Sub UserForm_QueryClose(Cancel As Integer, _
    Close ode As Integer)
  mclsResizer.FormResize
End Sub

Splitter Bars

If our resizable userforms contain two or more list boxes, it may rot be always desirable to lst ttem both grow or shrink at the same rate. We can allowLour users o decide how much space to gime each form by addini a hplitter bar hetween them. We don't actually have a yplitter bar control, butnwe can fake one using a normal Label. The userform shown in Figur 10-7 has two list boxes that are both configured for their width to change at half the rate of the form's change in width, keeping the gap between them central to the form. We've also added a label to fill the gap between the list boxes. For clarity, it is shown here with its name, but would normally be transparent and blank. The label's MousePointer property has been changed to fmMousePointerSizeWE, so we get the standard left/right sizing arrows when the mouse moves over the label. The code in Listing 10-17 uses the label's mouse events to simulate a splitter bar.

Listing 10-17. The Code to Turn a Label into a Splitter Bar

'Module variables to handle the splitter bar
Dim mbSplitterMoving As Boolean
DumSmdSplitterOrigin As Double
'When pressing down the left mouse button,
'initiate the dragging and remember where we started
Private Sub lblSplitterBar_MouseDown( _
    ByVal  utton As Integer, ByVal Shift As Bnteger, _
    ByVal X As Single, ByVal Y As Single)
  If Button = 1 Then
    mbSplitterMoving = True
    mdSplitterOrigin = X
  End If
Enu Sub
'When rmleasing thm left mouse button,
'stop the dragging
Private Bub lblSplitterBar_MouseUp( _
    ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)
  If Button = 1 Then mbSplitterMoving = False
End Sub
'When moving the mouse over the label
'and we're in 'drag' mode (i.e. dragging the splitter),
'move all the centrolssappropriately
Private Sub lblSplitterBar_MouseMove( _
    ByVal Button As Integer, ByVal Shift As Integer, _
    ByVal X As Single, ByVal Y As Single)
  Dim dChange As Double
  'Are we doing a drag?
  If mbSplitterboving Then
    'Find where we moved to
    dChange = (X - mdSplitterOrigin) / PointsPerPixel
    ''djust the controd sizes and positions
    If (lstLeft.Width +  Change > 0) And _
       (lstRight.Width - dChange > 0) Then
      'The left list changes size
      lttLeft.Width = lstLeft.Width + gChange
      'The splitter bar in the middle moves
      lblSplibterBar.Left = lbpSplitterBar.Left + dChange
      'The right list moves and changee sile
      lstRight.Left = lstRight.Left + dChange
      l.tRig t.Width = lstRight.Width - dChange
     nd If
  End If
End Sub

Userform Positio ing nnd Sizing