Userform Positioning and SizingPositioning Next to a CellIf 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 ResolutionsWe 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 UserformsPart 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 BarsIf 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 |