Hi all,

The following code succesfully creates a dialog box on the fly with
checkboxes created from a list in a worksheet.

However l would like to add some text to the dialog box.

I would have expected that writing the following code :

..Text = "Text l want to insert"

within the section ' Set dialog height, width, and caption would have
done the trick. However all it does is amend the caption.

Does anybody know how to do the above.

All suggestions greatfully received.

Regards

Michael Beckinsale


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

' Worksheets("SunJournal").Visible = False
' Worksheets("Actuals").Visible = False
' Worksheets("Consolidated").Visible = False
' Worksheets("OCSModDetail").Visible = False

Set Startsheet = ActiveSheet

' 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
Sheets("Sheet1").Select
Range("D3").Select
cc = ActiveCell.CurrentRegion.Count
For i = 1 To cc
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
ActiveCell.Value
TopPos = TopPos + 13
ActiveCell.Offset(1, 0).Select
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 UIN's to be extracted"
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
If vbCancel = True Then
' Delete temporary dialog sheet (without a warning)
CurrentSheet.Activate
Application.DisplayAlerts = False
PrintDlg.Delete
Worksheets("SunJournal").Visible = xlVeryHidden
Worksheets("Actuals").Visible = xlVeryHidden
Worksheets("Consolidated").Visible = xlVeryHidden
Worksheets("OCSModDetail").Visible = xlVeryHidden
Exit Sub
Else
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Sheets("OCS Template").Select
Sheets("OCS Template").Copy
Befo=Sheets("End")
Sheets("OCS Template (2)").Select
Sheets("OCS Template (2)").Name =
cb.Caption
End If
Next cb
End If
Else
' Reactivate original sheet
Startsheet.Activate
Range("A1").Select
End If

End If

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

' Worksheets("SunJournal").Visible = xlVeryHidden
' Worksheets("Actuals").Visible = xlVeryHidden
' Worksheets("Consolidated").Visible = xlVeryHidden
' Worksheets("OCSModDetail").Visible = xlVeryHidden

End Sub

If you want text to appear somewhere in the dialog you would first have to
add a label control and put the text in it. (If I follow you).

--
Jim Rech
Excel MVP

If you want to display some text in the dialog, then use a label.

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
Hi all,

The following code succesfully creates a dialog box on the fly with
checkboxes created from a list in a worksheet.

However l would like to add some text to the dialog box.

I would have expected that writing the following code :

.Text = "Text l want to insert"

within the section ' Set dialog height, width, and caption would have
done the trick. However all it does is amend the caption.

Does anybody know how to do the above.

All suggestions greatfully received.

Regards

Michael Beckinsale


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

' Worksheets("SunJournal").Visible = False
' Worksheets("Actuals").Visible = False
' Worksheets("Consolidated").Visible = False
' Worksheets("OCSModDetail").Visible = False

Set Startsheet = ActiveSheet

' 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
Sheets("Sheet1").Select
Range("D3").Select
cc = ActiveCell.CurrentRegion.Count
For i = 1 To cc
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
ActiveCell.Value
TopPos = TopPos + 13
ActiveCell.Offset(1, 0).Select
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 UIN's to be extracted"
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
If vbCancel = True Then
' Delete temporary dialog sheet (without a warning)
CurrentSheet.Activate
Application.DisplayAlerts = False
PrintDlg.Delete
Worksheets("SunJournal").Visible = xlVeryHidden
Worksheets("Actuals").Visible = xlVeryHidden
Worksheets("Consolidated").Visible = xlVeryHidden
Worksheets("OCSModDetail").Visible = xlVeryHidden
Exit Sub
Else
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Sheets("OCS Template").Select
Sheets("OCS Template").Copy
Befo=Sheets("End")
Sheets("OCS Template (2)").Select
Sheets("OCS Template (2)").Name =
cb.Caption
End If
Next cb
End If
Else
' Reactivate original sheet
Startsheet.Activate
Range("A1").Select
End If

End If

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

' Worksheets("SunJournal").Visible = xlVeryHidden
' Worksheets("Actuals").Visible = xlVeryHidden
' Worksheets("Consolidated").Visible = xlVeryHidden
' Worksheets("OCSModDetail").Visible = xlVeryHidden

End Sub