Dual Monitors And UserForms

Category: General VBA / UserForms | [Item URL]

 A UserForm object has a property named StartUpPosition. By default, this property is set to 1 (CenterOwner), which means that it should appear in the center of Excel's window.

This works fine, unless you have a dual-monitor system. In such a case, the UserForm is not centered in the Excel window. On my system, UserForms always display on the left side of the secondary monitor, even if Excel is maximized on the primary monitor.

To force an Excel UserForm to be centered in Excel's window, use this code to display the UserForm:

With UserForm1
  .StartUpPosition = 0
  .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
  .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  .Show
End With

I was hoping this would be fixed in Excel 2010, but nothing has changed. I'm sure most of the Microsoft programmers use at least two monitors, so they are obviously aware of the problem.

Pasting An Image To A UserForm Control

Category: Charts & Graphics / UserForms | [Item URL]

VBA programmers are familiar with Excel's UserForms. The UserForm object, as well as many of the ActiveX controls that are placed on a UserForm, can display an image -- the object's Picture property.

How to set the Picture property

If you believe Excel's online help, you'll think that there are two ways to set the Picture property for an object:

• At design time: Browse for an image file, by clicking the "..." button in the Properties box
• At run-time: Use VBA's LoadPicture function to retrieve an image file.

But there's another way. And it doesn't even require that the image is stored in a file. Here's the trick:

1. Locate the image that you want to use, and copy it to the Clipboard.
2. Activate the VB Editor and select the object that will contain the image (i.e., a UserForm, or a control that has a Picture property).
3. Make sure the Properties window is displayed (press F4 it it's not).
4. Click the Picture item. By default, this displays "(None)" as in the figure above.
5. Press Ctrl+V to paste the image on the Clipboard to the control.

More

Importantly, the picture that you paste to a control need not be stored in a file. Any picture that can be copied to the Clipboard can be pasted to an object's Picture property. And this includes charts stored in a worksheet. The trick here is to copy the chart as a picture: Click the chart, press Shift, and choose Copy Picture from Excel's Edit menu. Once copied, you can paste the picture of the chart to your UserForm control.

When pasting a chart, it's important to understand that the picture is not linked to the original chart. So if the chart changes, the picture will not change.

Displaying Help

Category: General VBA / UserForms | [Item URL]

A companion file is available: Click here to download

This tip describes a useful technique that lets you display a series of help topics in an Excel workbook.

How it works

The technique described here uses a UserForm, and the UserForm has the following controls (see the Figure below):

• A DropDown control (which displays the current help topic title)
• A Label control that displays the help topic text
• A Frame control. The label is inside of the frame to allow scrolling if all of the text isn't visible.
• Three buttons: Previous, Next, and Exit

The help topics are contained on a worksheet, and the VBA code simply reads the text in the worksheet and transfers it to the UserForm. The user can select a help topic from the DropDown, or view the topics sequentially using the Previous and Next buttons.

You can easily adapt this technique to your own project:

1. Export the UserForm and Import it to your project
2. Set up the worksheet with the help topics and help text
3. Create a simple macro to display the UserForm

Pros and Cons

This technique is very easy to set up, and requires no additional software or files. The VBA code is contained in the UserForm, so no additional programming is necessary. The primary disadvantage is that you cannot format the text that is displayed.

General Userform Tips

Category: UserForms | [Item URL]

One of the most useful features in Excel is the ability to create custom dialog boxes (UserForms). Before embarking on this task, ask yourself if a custom dialog box is really necessary. In some cases you may be able to use one of the following options to get user input:

• VBA's MsgBox function
• VBA's InputBox function (always returns a string)
• Excel's InputBox function (you can specify the type of data returned)
• Excel's GetOpenFilename method
• Excel's GetSaveAsFilename method
• Excel's FileDialog object (Excel 2002 only)

And, don't forget that your VBA code can display most of Excel's built-in dialog boxes. For example, the following subroutine displays the Move or Copy dialog (used to move or copy a sheet):

Sub ShowMoveCopy()
    Application.Dialogs(xlDialogWorkbookMove).Show
End Sub

When designing your userForm, a good rule of thumb is to make the dialog boxes look and work like those that are built into Excel. If you're a beginner, you may find it instructive to try to recreate a particular Excel dialog box. You may find a few situations in which a built-in dialog box can't be recreated exactly.

Checklist

Before you unleash your UserForm, give it one final check using the following checklist.

1. Are the controls aligned with each other?
2. Are similar controls the same size?
3. Are controls evenly spaced?
4. Is the dialog box too overwhelming? If so, you may want to use a series of dialogs (like a Wizard), or use a MultiPage control.
5. Can every control be accessed with an accelerator key?
6. Are any of the accelerator keys duplicated?
7. Are the controls grouped logically (by function)?
8. Is the tab order set correctly? The user should be able to tab through the dialog box and access the controls sequentially.
9. If the UserForm will be stored in an add-in, did you test it thoroughly after creating the add-in? It's important to remember that an add-in will never be the active workbook.
10. Will your VBA code take appropriate action if the UserForm is canceled, or if the user presses Esc?
11. Are there any misspellings in the text? Excel's spelling checker doesn't work with UserForms.
12. Does the UserForm have an appropriate caption?
13. If applicable, will your UserForm fit on the screen in lower screen resolutions?
14. If your UserForm will be used in multiple versions of Excel, have you tested the application on all versions?
15. If you use help, is the help topic correct? And does it explain all the controls?

Selecting A Directory

Category: UserForms | [Item URL]

If your Excel application needs to prompt the user for a directory, you may have been surprised to discover that there is no direct way to do this. You can use the GetOpenFileName method to display a dialog that prompts for a file name, but there is no way to display a dialog box that shows only directories.

In this document I present a function (named GetDirectory) that displays the dialog box shown below, and returns a string that represents the selected directory. If the user clicks cancel, the function returns an empty string.

The GetDirectory function takes one argument, which is optional. This argument is a string that will be displayed in the dialog box. If the argument is omitted, the dialog box displays Select a folder as the message.

The GetDirectory function

The GetDirectory function is listed below. To use this function, copy the text and paste it to a VBA module.

Option Explicit
Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
  As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Sub Test()
    Dim Msg As String
    Msg = "Please select a location for the backup."

    MsgBox GetDirectory(Msg)
End Sub

Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&

'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1

'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function

Using the function

The simple subroutine listed below demonstrates how to use the GetDirectory function in your code. Executing this subroutine displays the dialog box. When the user clicks OK or Cancel, the MsgBox function displays the full path of the selected directory.

Sub Test()
    Dim Msg As String
    Msg = "Please select a location for the backup."
    MsgBox GetDirectory(Msg)
End Sub

Specifying the starting directory

If you would like to be able to specify the starting directory, things get a lot more complicated.

NOTE: If you use Excel 2002 or later you can prompt the user to select a directory by using the FileDialog object. The advantage is that you can specify a starting directory.

Displaying A Progress Indicator

Category: UserForms | [Item URL]

A companion file is available: Click here to download

A common question among Excel developers is, How can I use a custom dialog box to display the progress of a lengthy macro?

This document describes how to create an attractive progress indicator with minimal effort.

Creating the UserForm

Follow the steps below to create the progress indicator UserForm.

1. Insert a new UserForm and change its Caption to Progress.
2. Add a Frame control and name it FrameProgress.
3. Add a Label control inside of the Frame and name it LabelProgress. Remove the Label's caption, and make its background color red.
4. Add another label (option) to describe what's going on.
5. Adjust the form and controls so they look like this:

Creating the Event-handler subroutines

The trick here involves running a subroutine automatically when the dialog box is displayed. Since the Initialize event occurs before the dialog box is actually show, you must use the Activate event. Insert the following subroutine in the Code window for the UserForm. This subroutine simply calls the Main subroutine (stored in a VBA module) when the UserForm is displayed.

Private Sub UserForm_activate()
    Call Main
End Sub

The Main subroutine is listed below. This demo routine simply inserts random numbers into the active worksheet. As it does so, it changes the width of the Label control and displays the percent completed in the Frame's caption. You will, of course, substitute your own subroutine. And you'll need to figure out how to determine the progress complete.

Sub Main()
'   Inserts random numbers on the active worksheet
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single
    
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Cells.Clear
    Application.ScreenUpdating = False
    Counter = 1
    RowMax = 100
    ColMax = 25
    For r = 1 To RowMax
        For c = 1 To ColMax
            Cells(r, c) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next c
        PctDone = Counter / (RowMax * ColMax)
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
'       The DoEvents statement is responsible for the form updating
        DoEvents
    Next r
    Unload UserForm1
End Sub

Creating the start-up subroutine

All that's missing is a subroutine to display the dialog box. Enter the following subroutine in a VBA module.

Sub ShowDialog()
    UserForm1.LabelProgress.Width = 0
    UserForm1.Show
End Sub

How it works

When you execute the ShowDialog subroutine, the Label object's width is set to 0. Then the Show method of the UserForm1 object displays the dialog box. When the dialog box is displayed, its Activate event is triggered, which executes the Main subroutine. The Main subroutine periodically updates the width of the Label. Notice the DoEvents statement. Without this statement, changes to the Label are not visible. Before the subroutine ends, the last statement unloads the UserForm object.

Caveat

This is definitely a slick technique, but you should be aware that it may slow down your macro a bit due to the extra overhead of updating the progress indicator. In many cases, however, it is not even noticeable.

Importing And Exporting Userforms

Category: UserForms | [Item URL]

If you've worked with UserForms, you've undoubtedly noticed that inserting a new UserForm results in an empty dialog box. You might prefer to add a UserForm that already has some controls on it (for example, an OK button and a Cancel button).

In this document I describe a simple technique that lets you create a new "default" UserForm that contains these two buttons. The procedure can be adapted for other controls.

Creating the default UserForm

Follow these steps to create a UserForm.

1. Start with a blank workbook.
2. Press Alt+F11 to activate the Visual Basic Editor (VBE)
3. In the Project window, select the blank workbook
4. Select the Insert UserForm command. An empty UserForm is added to the project.
5. Use the Toolbox and add a CommandButton to the form.
6. Change the following properties for the CommandButton:
   
   Name: OKButton
   Caption: OK
   Default: True
   
   
7. Use the Toolbox and add a second CommandButton to the form.
8. Change the following properties for this CommandButton:
   
   Name: CancelButton
   Caption: Cancel
   Cancel: True
   
   
9. Double-click the Cancel button to activate the Code window for the UserForm.
10. Modify the CancelButton_Click subroutine as follows:

Private Sub Cancel_Button_Click()
    Unload Me
End Sub

Exporting the UserForm

The next step is to export this UserForm.

1. Make sure the UserForm is selected in the Project window.
2. Select the File Export File command.
3. Enter a name for the exported UserForm. Use a descriptive name, like NewDefaultForm

The preceding steps saved the UserForm and code to a file.

Importing the UserForm

When you need to add a new UserForm to a project, you can save time by importing the file you saved.

1. Make sure your project is selected in the Project window.
2. Select the File Import File command.
3. Locate the file you exported in the previous section.
4. Use the Properties window to give the new form a descriptive name. This step is necessary if you later decide to import the file again to add another UserForm.

Handle Multiple Userform Buttons With One Subroutine

Category: UserForms | [Item URL]

A companion file is available: Click here to download

When you create a UserForm, every control on the form must have its own event handler procedure. For example, if a UserForm has 12 CommandButtons, you need 12 procedures to handle the click events for those buttons.

This tip describes a way around this limitation by using a Class Module to define a new class.

Procedure

1. Create your UserForm as usual, and add several CommandButtons.
2. Insert a Class Module and enter the following code. You will need to customize the ButtonGroup_Click subroutine.

Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
    MsgBox "Hello from " & ButtonGroup.Name
End Sub

3. Insert a normal module and enter the following code. In this code, I exclude a button named OKButton from the "button group." Therefore, clicking the OK Button does not execute the ButtonGroup_Click subroutine.

Option Explicit

Dim Buttons() As New Class1

Sub ShowDialog()
    Dim ButtonCount As Integer
    Dim ctl As Control

'   Create the Button objects
    ButtonCount = 0
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "CommandButton" Then
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
               Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl
    UserForm1.Show
End Sub

4. Execute the ShowDialog subroutine to display the UserForm. Clicking any of the CommandButtons (except the OKButton) executes the ButtonGroup_Click subroutine.

Note: To use this technique with other types of controls, change the WithEvents statement. For example:

   Public WithEvents LabelGroup As MSForms.Label

Notice that you must qualify the Label object because Excel also has an object named Label. Also, you will need to make appropriate changes throughout the ShowDialog procedure.

Filling A Listbox With Unique Items

Category: UserForms | [Item URL]

A companion file is available: Click here to download

When you display a list of items in a ListBox, you generally don't want to show duplicate items. This tip describes a clever way of filling a UserForm with unique items from a list. This technique is adapted from a tip by J.G. Hussey, published in Visual Basic Programmer's Journal.

How it works

This tip uses a Collection object, and relies on the fact that VBA generates an error if you attempt to add an item to a collection when the item already exists in the collection. The trick is to build the collection by adding all items to it, and ignore the errors that may occur. The result is a collection of unduplicated items.

Example

I created an example to demonstrate the technique. The items (105 of them) are stored in Column A of a worksheet. Many of these items are duplicated. The RemoveDuplicates subroutine, listed below, builds a collection that consists of the unique items in the list. It then transfers the items to a ListBox on a UserForm.

Listing

Following is the VBA listing.

Option Explicit
'   This example is based on a tip by J.G. Hussey,
'   published in "Visual Basic Programmer's Journal"

Sub RemoveDuplicates()
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    
'   The items are in A1:A105
    Set AllCells = Range("A1:A105")
    

'   The next statement ignores the error caused
'   by attempting to add a duplicate key to the collection.
'   The duplicate is not added - which is just what we want!
    On Error Resume Next
    For Each Cell In AllCells
        NoDupes.Add Cell.Value, CStr(Cell.Value)
'       Note: the 2nd argument (key) for the Add method must be a string
    Next Cell

'   Resume normal error handling
    On Error GoTo 0

'   Sort the collection (optional)
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i

'   Add the sorted, non-duplicated items to a ListBox
    For Each Item In NoDupes
        UserForm1.ListBox1.AddItem Item
    Next Item

'   Show the UserForm
    UserForm1.Show
End Sub

Displaying A Menu Of Worksheets To Print

Category: Printing / UserForms | [Item URL]

This tip contains a VBA subroutine that displays a dialog box that contains the names of all non-empty worksheets in the active workbook. The names are displayed as checkboxes. The user can select which sheets to print, and click OK. The macro then prints the selected worksheets.

This subroutine is rather unusual since it creates the dialog box on the fly, and then deletes it after it is dismissed.

The code below prints the selected worksheets, but this general technique can be adapted to other situations in which you need the user to specify one or more sheets in a workbook.

Although this procedure uses an Excel 5/95 Dialog Sheet, it will also work with later versions of Excel. By the way, this is a rare example of how using a Dialog Sheet is easier than using a UserForm. Creating a UserForm on the fly is much more difficult, but it is possible.

Using the code

To try out the example, copy the code below and paste it to a VBA module (there is no need to add a dialog sheet). Then execute the SelectSheets subroutine.

The SelectSheets subroutine

Option Explicit

Sub SelectSheets()
    Dim i As Integer
    Dim TopPos As Integer
    Dim SheetCount As Integer
    Dim PrintDlg As DialogSheet
    Dim CurrentSheet As Worksheet
    Dim cb As CheckBox
    Application.ScreenUpdating = False

'   Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical
        Exit Sub
    End If

'   Add a temporary dialog sheet
    Set CurrentSheet = ActiveSheet
    Set PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

'   Add the checkboxes

    TopPos = 40
    For i = 1 To ActiveWorkbook.Worksheets.Count
        Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'       Skip empty sheets and hidden sheets
        If Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible Then
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        End If
    Next i

'   Move the OK and Cancel buttons
    PrintDlg.Buttons.Left = 240

'   Set dialog height, width, and caption
    With PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"

    End With

'   Change tab order of OK and Cancel buttons
'   so the 1st option button will have the focus
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
'                   ActiveSheet.PrintPreview 'for debugging

                End If
            Next cb
        End If
    Else
        MsgBox "All worksheets are empty."
    End If

'   Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False
    PrintDlg.Delete

'   Reactivate original sheet
    CurrentSheet.Activate
End Sub

NOTE: Aaron Blood suggested the following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.

    If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    Worksheets(cb.Caption).Select Replace:=False 
                End If
            Next cb
            ActiveWindow.SelectedSheets.PrintOut copies:=1 
            ActiveSheet.Select 
    End If

Page 1 of 2 pages
[Next page]