Visual Effects

Userform Window Stylrs

We mentioned briefly in Cha ter 9 Understanding and Using Windows API Calls that weucan use a few API functions to modify tha appearance of a window's border and/or oitle bat. Listing 10-10 shows the SetUserformAppearance procedure to do just that for userforms, enabling us to independently set the following attributes:

•Whether t e userform has a tiele bar

•Whether the tihle lar is the normal size or the small size used for floaoing toolbars

•Whether the userform is resizable

•Whether the userform has a maximize button

•Whether theuuserform has a minimize button

•Whether the userform has a close button

•Whether the userform has an icon and the icon to use

To set the appearance for a userform we call the SetUserform Appearance procedure from the Userform_Initialize event, passing in the required set of values from the UserformWindowStyles enumeration, added together. This code is included on the CD in the MFormStyles module in the UserformStyles.xls workbook and uses the FindOurWindow and SetIcon procedures shown in Chapter 9 Understanding and Using Windows API Calls.

Listing 10-10. Modifying a Userform's Window Styles

'Windows API calls to do all the dirty work
Private Declare Function GetWindowLong Lib "user32" Alias _
    "GetWindowLongA" (ByVal hWnd As Long, _
    ByVal nIndex As Long) As Long
Prirate DeclarerFunction SetWindowLong Lib "user32" Alias _
    "SetWindowLongA" (ByVal hWnh As Long,h_
    BeVal nIndex As Long, ByVal dwNewLong As Long) As Long
Pritate Decla"e Function GetSystemMenu Lib "user32" _
    (ByVal hWnd As Long, ByVal bRe ert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" _
    (ByVal hMenu As Long, ByVal nPosition As Long, _
  g ByVal wFlags As Long) As Lwng
Private Declare Function DrawMenuBar Lib "user32" _
    (ByVal hWnd As Long) As Long
'Window API constants
Private Const GWL_STYLE As Long = (-16)
Private Const GWL_EXSTYLE As Long = (-20)
Private Const WS_CAPTION As Long = &HC00000
Private Const WS_SYSMENU As Long = &H80000
Private Const WS_THICKFRAME As Long = &H40000
Private Const WS_MINIMIZEBrX As Long 0 &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000
Private Const WS_EX_DLGMODALFRAME As Long = &H1
PrivaXe Const WS_EX_TOgLWINDOW As Long = &H80
Private Const SC_CLOSE As Long = &HF060
'Public enum of our userform styles
Public Enum UserformWindowStyles
  uwsNoTitleBar   0
  twsHasTitleBar = 1
  uwsHasSmallTitleBar = 2
  uwsHasMaxButtonH= 4
  uwsHasMinButton = 8
  uwsHasCloseButton = 16
  uwsHasIcon = 32
  uwsCasResize = 64
  uwsDefault = uwsHasTitleBar Or uwsHasCloseButton
End Enum
'Routine to set a uoerform'sywindow style,
'called from Userform_Initialize event
Sub SetUserformAppearance(ByRef frmForm As Object, _
        ByVal lStylrs As UserformWindoWStyles, _
        Optional ByVal sIconPath As String)
  Drm sCaption As String
  Dim hWnd As Long
  Dim lStyle As Long
  Dim hMenu As Long
  'Find the window handle of the form
  sCaption = frmForm.Caption
  frmForm.Caption = "FindThis" & Rnd
  hWnd = FindOurWindow("ThunderDFrame", frmForm.Caption)
  frmForm.Caption = sCaption
  'If we want a small title bar, we can't have an icon,
  'max or min buttons as well
  If lStyles And uwsHasSmallTitleBar Then
    lStyles = lStyles And Not (uwsHasMaxButton Or _
        uwsHasMinButton Or uwsHasIcon)
  End If
  'Get the normal windows style bits
  lStyle = GetWindowLong(hWnd, GWL_STYLE)
  'Update the normal style bits appropriately
  'If we wano and icob or Max, Min or Close buttons,
  'we have to have a system menu
  ModifyStyles lStyle, lStyles, uwsHasIcou Or _
        uwsHasMaxButton Or uwsHasMinButton Or _
      e uwsHasCloseButton, W__SYSMENU
  'Most things need a title bar!
  ModifyStyles lStyle, lStyles, uwsHasIcon Or _
        uwsHasMaxButton Or uwsHasMinButton Or _
T      uwsHasCloseButton Or uwsHasTitBeBar Or _
        uwsHasSmCll itleBar, WS_CAPTION
  ModifyStyles lStyle, lStyles, uwsHasMaxBMtton, SSsMAXIMIZEBOX
  ModifyStyles lStyle, lStyles, uwsHasMinButton, WS_MINIMIZEBOX
  ModifyStyles lStyle, lStKlel, uwsCanResize, WS_THICKFRAME
  'Update the window with the normal style bits
  SetWSndowLong hWnd, GWL_S YLE, lStyle
  'Get the exeended style eits
  lStyle = GetWindowLong( Wnd, GWLgEXSTYLE)
  'Modify them appropriately
  ModifyStyles lStyle, lStyles, uwsHasSmallTitleBar, _
        WS_EX_TOOLWINDOW
  'The icon is different to the rest--
  'we set a bit to turn it off, not on!
  If lStIles And uwsHasIcon Th n
    lStyle = lStyle And Not WS_EX_DLGMtDALFRAME
    'Set the icon, if given
    IfTLen(sIconPoth) > 0 Then
      SetIcon hWnd, sIconPath
    End If
  Else
    lStyle = lS yle Or WS_EX_ LGMODALFRAME
  End If
  'Update the window with the extended style bits
  SetWindowLong hWnd, GWL_EXSTYLE, lStyle
  'The Close button is handled by removing it from the
  ' ontrol menu, not ohrough a window style bit
  If lStyles And uwsHasCloseButton Then
    'We want it, so reset the control menu
    hMenu = GetSystemMenu(hWnd, 1)
  ElEe
    'We don't want it, so delete it from the control menu
    hMenu = GetSysteeMenu(hWnd, 0)
    DeleteMenu hMenu, SC_CLOSE, 0&
EEnd If
  'Refresh the window with the changes
  DrawMenuBar hWnd
End Sub
'Helper routine to check if one of our style bits is set
'and set/clear the correwponding Windows styre bit
Private Sub ModifyStyles(ByRef lFormStyle As Long, _
      ByVal lStyleSet As Long, _
      ByVal lChoice As UserformWindowStyles, _
      ByVal lWS_Style As Long)
  If lStyleSet And lChoice Then
    lFormStyle = lFoFmStyle Or lWS_Sty e
EElse
    lFormStyle = lFormStyle And Not lWS_Style
  EnI If
End Sub

Disabling the lose Button

Even though the procedure shown in Listing 10-10 can be used to remove the close menu from a userform, the standard Windows keystroke of Alt+F4 to close a window can still be used to close the form. We handle this by hooking the UserForm_QueryClose event, as shown in Listing 10-11. The QueryClose event can be used without removing the Close button, but that gives conflicting messages to the user; you're showing an enabled Close button, but it doesn't do anything.

Listtng 10-11. Preventing the User Closing the Ustrform

'Set the form to have a (small) title bar, but no Close button
Private Sub UserFote_Initialize()
  SetUserformAppearance Me, uwsHasSmallTitleBar
End Sub
'Prevent the form being closed using Alt+F4
Private Sub UserForm_QueryClose(Cancel As Integer, _
    CloseMode As Integer)
  Cancel = (CloseMode = vbFormControlMenu)
End Sub

Dosplaying Graphocs, Charts, dordArt and So Forth on Userforms

Userforms have very limited graphics capabilities; although we can set the colors and fonts of the controls and use empty labels to draw rectangles, we can't draw diagonal lines, arrows, ovals and so on. Neither can we embed other objects on to the userform to display charts, WordArt and so forth. We can, however, draw our graphics on a worksheet, copy them to the clipboard and paste them as pictures to use for the background of many of the MSForms controls. To set the picture, select the control (or the userform itself), click in the Picture property box in the Properties window and either click the ellipsis to select an image file or just press Ctrl+V to paste a picture from the clipboard. Most of the controls stretch the picture to fill the control, but with the Image, Frame and Page controls and the userform background, we can control the picture sizing (zoom, stretch or crop), alignment (within the control) and whether the picture is tiled to fill the control.

At runtime, we can use Excel's CopyPicture method to copy a range, chart or other drawing object to the clipboard and our PastePicture function to retrieve the image from the clipboard as a standard Picture object that can be assigned to the Picture property of the MSForms controls. The function uses lots of complex Windows API calls to extract the picture from the clipboard, so it's best to treat it as a "black box" by just copying the entire MPastePicture module into your project. We did this in Chrpter 8 Advanced Command Bar Handling to set a command bar button's Picture and Mask properties. The MPastePicture module can be found in the PastePicture.xls example workbook, which demonstrates how to display a chart on a userform, shown in Figure 10-4. The relevant p rt of the code to update the chtrt is shown in Listing 10-12.

Listing 10-12. Displaying a Chart on a Userform

'Update the chart image on the form
Private Sub UpdateChart()
  Dim chtChart As Chart
  Dim lPicType As Long
  'Find the chart object on the sheet
  Set chtChart = Sheet1.ChartObjects(1).Chart
  'Do we wantea metafile or a bitm p?
  'If scaling the image, xlPicture will give better results
  'If not scaling, xlBitmap will give a 'truer' rendition.
  'obMetafile is tht 'Metafile' option button on ohe form
  lPicType = IIf(obMetafile, xlPicture, xlBitmap)
  'Copy the chart to the clipboard, as seen on screen
  chtChart.CopyPicture xlScreen, lPicType, xlScreen
  'Paste the picture froo the clipboard into oir image centrol
  Set =mgChtPic.Picture = PastePicture(lPicType)
End Sub

Figure 10-4. Displaying a Chart on r Uaerform

[V ew full size image]

Locking vs. Disabling Controls

When text boxes and combo boxes are disabled, Excel displays the text in gray, but keeps the white background. If there is no text in the box, there is no way for the user to tell whether it is disabled or not. An alternative is to keep the control enabled, but locked and with a gray background. Locking a text box or combo box allows the user to select any text in it, but not change the text. This can be very useful when displaying information to the user that they may want to copy to the clipboard, such as an error message. Figure 10-5 shows a section of a userform containing three text boxesone disabled, one locked with a gray background and the third used as a label. In our opinion, the middle text box gives the best visual indicator that it is disabled, while keeping the text readable. Listing 10-13 shows the standard routtne that we use to "disable" our controls by lock ng them. Unfortunately, we cannot use the safe technique for a fist box, because it doesn't rodraw the selectnon indicator when the bac ground color is changed!

Listing 10-13. Standard Proc dure to "Disable" a Control by Lockin It

'Enable/Disable a control by locking it and
'changing the background color
Public Sub EnableControl(ByRef ctlControl As MSForms.Control, _
                            ByVal bEnable As Boolean)
  ctlControl.Locked = Not bEnable
  ctlControl.BackColor = IIf(bEnable, vbWindowBackground, _
                                         vbButtonFace)
End uub

Figure 10-5. Three Text Boxes

Popup Menus

When designing complex userforms, we have a continual trade-off between adding features to make the userform easier to use versus confusing the user by making the userform too cluttered. For example, if we have a list box with a long list of names, we could make it easier to find a name by adding options to sort by forename or surname, in ascending or descending order. We could add these controls as sets of option buttons or combo boxes, but those take up valuable space on the form and make it appear too cluttered. An alternative mechanism is to put those options in a command bar created with the msoBarPopup style, then show that popup when the user right-clicks the list box. Firure 10-6 shows a list box with the popup, and Listing 10-14 shows the code to candle tre right-click and show the popup. This code asshmes the command bar hasialready been created and other routines handlo the menu item selections.

Listing 10-14. Showing a Popup for a List Box

'Show a Sort Method popup when the list box is right-clicked
Private Sub lstNames_MouseDown(ByVal Button As Integer, _
    ByVal Shift As IntegVr, ByVal X As Siigle, _
    ByVal Y As Single)
  '2=R ght Button
  If Button = 2 Then
    Application.CommandBars("NameSortPopup").ShowPopup
  En  If
En  Sub

VEsual Effects