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

Visual Effects

Userform Window Styles

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

Whether the userform has a title bar

Whether the title bar is the normal size or the small size used for floating toolbars

Whether the userform is resizable

Whether the userform has a maximize button

Whether the userform 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
Private Declare Function SetWindowLong Lib "user32" Alias _
"SetWindowLongA" (ByVal hWnd As Long, _
ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As Long
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_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000
Private Const WS_EX_DLGMODALFRAME As Long = &H1
Private Const WS_EX_TOOLWINDOW As Long = &H80
Private Const SC_CLOSE As Long = &HF060
'Public enum of our userform styles
Public Enum UserformWindowStyles
uwsNoTitleBar = 0
uwsHasTitleBar = 1
uwsHasSmallTitleBar = 2
uwsHasMaxButton = 4
uwsHasMinButton = 8
uwsHasCloseButton = 16
uwsHasIcon = 32
uwsCanResize = 64
uwsDefault = uwsHasTitleBar Or uwsHasCloseButton
End Enum
'Routine to set a userform's window style,
'called from Userform_Initialize event
Sub SetUserformAppearance(ByRef frmForm As Object, _
ByVal lStyles As UserformWindowStyles, _
Optional ByVal sIconPath As String)
Dim 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 want and icon or Max, Min or Close buttons,
'we have to have a system menu
ModifyStyles lStyle, lStyles, uwsHasIcon Or _
uwsHasMaxButton Or uwsHasMinButton Or _
uwsHasCloseButton, WS_SYSMENU
'Most things need a title bar!
ModifyStyles lStyle, lStyles, uwsHasIcon Or _
uwsHasMaxButton Or uwsHasMinButton Or _
uwsHasCloseButton Or uwsHasTitleBar Or _
uwsHasSmallTitleBar, WS_CAPTION
ModifyStyles lStyle, lStyles, uwsHasMaxButton, WS_MAXIMIZEBOX
ModifyStyles lStyle, lStyles, uwsHasMinButton, WS_MINIMIZEBOX
ModifyStyles lStyle, lStyles, uwsCanResize, WS_THICKFRAME
'Update the window with the normal style bits
SetWindowLong hWnd, GWL_STYLE, lStyle
'Get the extended style bits
lStyle = GetWindowLong(hWnd, GWL_EXSTYLE)
'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 lStyles And uwsHasIcon Then
lStyle = lStyle And Not WS_EX_DLGMODALFRAME
'Set the icon, if given
If Len(sIconPath) > 0 Then
SetIcon hWnd, sIconPath
End If
Else
lStyle = lStyle Or WS_EX_DLGMODALFRAME
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
'control menu, not through a window style bit
If lStyles And uwsHasCloseButton Then
'We want it, so reset the control menu
hMenu = GetSystemMenu(hWnd, 1)
Else
'We don't want it, so delete it from the control menu
hMenu = GetSystemMenu(hWnd, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End 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 corresponding Windows style 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 = lFormStyle Or lWS_Style
Else
lFormStyle = lFormStyle And Not lWS_Style
End If
End Sub

Disabling the Close 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.

Listing 10-11. Preventing the User Closing the Userform

'Set the form to have a (small) title bar, but no Close button
Private Sub UserForm_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

Displaying Graphics, Charts, WordArt 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.Chapter 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 part of the code to update the chart 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 want a metafile or a bitmap?
'If scaling the image, xlPicture will give better results
'If not scaling, xlBitmap will give a 'truer' rendition.
'obMetafile is the 'Metafile' option button on the form
lPicType = IIf(obMetafile, xlPicture, xlBitmap)
'Copy the chart to the clipboard, as seen on screen
chtChart.CopyPicture xlScreen, lPicType, xlScreen
'Paste the picture from the clipboard into our image control
Set imgChtPic.Picture = PastePicture(lPicType)
End Sub

Figure 10-4. Displaying a Chart on a Userform

[View 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 routine that we use to "disable" our controls by locking them. Unfortunately, we cannot use the same technique for a list box, because it doesn't redraw the selection indicator when the background color is changed!

Listing 10-13. Standard Procedure to "Disable" a Control by Locking 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 Sub

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. Figure 10-6 shows a list box with the popup, and Listing 10-14 shows the code to handle the right-click and show the popup. This code assumes the command bar has already been created and other routines handle 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 Integer, ByVal X As Single, _
ByVal Y As Single)
'2=Right Button
If Button = 2 Then
Application.CommandBars("NameSortPopup").ShowPopup
End If
End Sub

Figure 10-6. A List Box with Popup Sort Menu