The 'Common Dialog' technique described here is a technique used in a number of solutions presented here. These solutions are:

This techniques is based on an idea that I first encountered in the Excel newsgroups, and was given by the original poster as a technique that he wanted amended to his solution. He had originally got the idea from a page on John Walkenbach's site, Displaying a Menu of Worksheets to Print. I liked the technique so much that I gave a number of subsequent solutions in the newsgroups using adaptations of this technique. These pages result from those solutions.

John's example, and all of the examples on this site, use the worksheets collection, but any collection of objects could be used, as long as there is a way to access each member of the collection.

Below, an explanation of the technique is provided by dissecting the code. As all of the examples at this site utilise the worksheets collection, the explanation also is based around worksheets.

First,

Dim fCancel As Boolean
			

Further procedure level variables are defined for use in the code, including 3 constants defining the look and feel of the dialog that are amende for each particular solution. The other solution specific variable is the type of control being used. The examples at this site use checboxes, optionbuttons, and editboxes, but in principle an y forms control could be used. Note that the code here refers to a generic control and calls it type_of_control.

Const sTitle As String = "Custom Sheet Order"
Const sMsgTitle As String = "Custom Order"
Const sID As String = "___CustOrder"
Dim dlgThis As DialogSheet
Dim oThis As Workbook
Dim CurrentSheet As Worksheet
Dim oCtl As type_of_control
Dim nBinary As Long
Dim cMaxLetters As Long
Dim i As Long
Dim j As Long
Dim iTopPos As Long
Dim iItem As Long
Dim aryOrder()
			

First, screenupdating is turned off to stop the screen flashing.

    Application.ScreenUpdating = False
			

A check is made that the workbook is not protected, exiting if so.

    If oThis.ProtectStructure Then
        MsgBox "Workbook is protected.", vbCritical, sMsgTitle
        Exit Sub
    End If
			

The current worksheet is saved, to restore later, and a dialog sheet is added, given a special name, and then hidden.

    Set CurrentSheet = ActiveSheet
    Set printDlg = oThis.DialogSheets.Add
    With dlgThis

        .Name = sID
        .Visible = xlSheetHidden
			

The special controls are added in a loop, one for each sheet. The length of the longest sheet name is saved for positioning the controls later, thereby ensuring aesthetic appeal of the dialog. The loop also controls the vertical positioning of each row.

        Item = 0

        TopPos = 40
        cMaxLetters = 0
        For i = 1 To oThis.Sheets.Count
            'skip hidden sheets
            If oThis.Sheets(i).Visible <> xlSheetHidden Then
                'ignore newly added dialog sheet
                If oThis.Sheets(i).Name <> sID Then
                    'calculate length of longest sheet name
                    If Len(oThis.Sheets(i).Name) > cMaxLetters Then
                        cMaxLetters = Len(oThis.Sheets(i).Name)
                    End If
                    Item = Item + 1
                    'add a label with sheet name
                    .Labels.Add 78, TopPos, 150, 16.5
                    .Labels(iItem).Text = _
                                oThis.Sheets(i).Name
                    'and an type_of_control with its sheet index
                    ... add the control type particular to
                    ... each solution
                    TopPos = TopPos + 13
                End If
            End If
        Next i
			

Then the controls and the buttons are adjusted to make it organised and readable,

        'position the controls and buttons according to
        '   length of longest sheet name
        .type_of_control.Left = 78 + (cMaxLetters * 4) + 10
        .Buttons.Left = 78 + (cMaxLetters * 4) + 10 + 24 + 8

        'adjust dialog to align with number of controls and
        '    length of longest sheet name
        With .DialogFrame
            .Height = Application.Max(68, .Top + TopPos - 34)
            .Width = 78 + (cMaxLetters * 4) + 10 + 24 + 8 - 10
            .Caption = sTitle
        End With
			

The 'OK' and 'Cancel' buttons are juggled so that the 'OK' button gets the focus, and a macro is attached to the 'Cancel' button.

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

        .Buttons("Button 3").OnAction = "CancelButton"
			

And then we loop showing the dialog and getting input,

        Application.ScreenUpdating = True
        Do
            If .Show Then
                fCancel = False
                nBinary = 0
                For Each oCtl In 'type_of_control
                    'take action according to the solution
                     'such as select sheet, hide sheet(s), etc.
                Next oCtl
            End If
        Loop Until fCancel Or 'other condition satisfied
        	

Take any solution specific action, and then tidy-up,

        'If everything OK and not cancel
        If Not fCancel Then
            ReDim aryOrder(1 To .EditBoxes.Count)
            For i = 1 To type_of_control.Count
                'act according to specific requirement
            Next i
        End If

        Application.DisplayAlerts = False
        .Delete

    End With

End Sub
			

Private Sub CancelButton()
    fCancel = True
End Sub
			

Clearly, this code needs to be adjusted for each particular solution, but this overview gives an outline of the essential technique, and shows where to insert the solution specific code. The individual solutions show this more clearly in their implementations.