Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

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. Chapter 9 Understanding and Using Windows API Calls.

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

'API Functions to find a window and read its position
Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetWindowRect Lib "user32" _
(ByVal hWnd As Long, lpRect As RECT) As Long
Private Type RECT
Left As Long
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 object at the cell, activate 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)
.Activate
.Delete
End With
'Find the EXCELE window
hWndDesk = FindWindowEx(ApphWnd, 0, "XLDESK", vbNullString)
hWndChart = FindWindowEx(hWndDesk, 0, "EXCELE", vbNullString)
'Read its position
GetWindowRect hWndChart, uChartPos
'Move the form to the same position,
'converting pixels to points
frmForm.Left = uChartPos.Left * PointsPerPixel
frmForm.Top = uChartPos.Top * PointsPerPixel
End Sub
'Test procedure to show a form next 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
MoveFormToCell frmForm, ActiveCell.Offset(0, 1)
'Show the form
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:

We can't fit many controls on a 640x480 userform.

Userforms that fit on a 640x480 screen often make very poor use 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 Userforms

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
Dim mclsResizer As CFormResizer
'The Resizer class is set up in UserForm_Initialize
Private Sub UserForm_Initialize()
'Create the instance of the class
Set mclsResizer = New CFormResizer
'Tell it which form it's handling
Set mclsResizer.Form = Me
End Sub
'When the form is resized, the UserForm_Resize event is
'raised, which we just pass on to the Resizer class
Private Sub UserForm_Resize()
mclsResizer.FormResize
End Sub
'The QueryClose event is called whenever the form is closed.
'We call the FormResize method one last time, to store the
'form's final size and position in the registry
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
mclsResizer.FormResize
End Sub

Splitter Bars

If our resizable userforms contain two or more list boxes, it may not be always desirable to let them both grow or shrink at the same rate. We can allow our users to decide how much space to give each form by adding a splitter bar between them. We don't actually have a splitter bar control, but we can fake one using a normal Label. The userform shown in Figure 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
Dim mdSplitterOrigin As Double
'When pressing down the left mouse button,
'initiate the dragging and remember where we started
Private Sub lblSplitterBar_MouseDown( _
ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
If Button = 1 Then
mbSplitterMoving = True
mdSplitterOrigin = X
End If
End Sub
'When releasing the left mouse button,
'stop the dragging
Private Sub 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 controls appropriately
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 mbSplitterMoving Then
'Find where we moved to
dChange = (X - mdSplitterOrigin) / PointsPerPixel
'Adjust the control sizes and positions
If (lstLeft.Width + dChange > 0) And _
(lstRight.Width - dChange > 0) Then
'The left list changes size
lstLeft.Width = lstLeft.Width + dChange
'The splitter bar in the middle moves
lblSplitterBar.Left = lblSplitterBar.Left + dChange
'The right list moves and changes size
lstRight.Left = lstRight.Left + dChange
lstRight.Width = lstRight.Width - dChange
End If
End If
End Sub

Figure 10-7. A Splitter Bar Between Two List Boxes