Add a scroll-bar to a dialogue-box

[jammoca]

I'm currently using the following code that provides a pop-up dialogue-box that lists all non-empty worksheets within a workbook and places a checkbox beside each one. The user can then tick multiple checkboxes, press 'OK' and this will print the corresponding worksheets that were selected. The pop-up dialogue-box then disappears.

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

However, as my workbook has grown in the number of worksheets contained within it, the pop-up now extends beyond the limits of the computer screen, and while the pop-up is visible, I'm unable to scroll the screen.

What I would like is for my pop-up to have a scroll-bar added to the side.

Is this possible ?

Kind regards,

Chris

 

[starl]

A dialogsheet? Wow.. I didn't think anyone used those anymore. Sorry, can't help you with that, but you may want to consider updating the workbook and using a userform with a listbox (which can have checkboxes and scrollbars!). Who knows how much longer MS will support dialogsheets.

 

[jammoca]

Ok, I'm willing to try anything.

How do I create a userform and how would I make it do what I'm describing above ?

Is it something simple, or should I teach myself userforms etc ?

Kind regards,

Chris

 

[starl]

You are already coding, so working with userforms shouldn't be very difficult for you.
In the VBE, go to Insert, Userform - like you would a standard module.You can design the userform - I suggest a listbox for what you want, and add code behind it. Userforms aren't just forms - they have code, too. As a matter of fact, you probably want to take your current code and make it the Initalize event for the form. You'll need to change the properties of the listbox to get the checkboxes and allow multiselect. And I'm sure there are examples here for loading listboxes and working with them. There are different ways and you may find you prefer one method over another.