Dialogue box check boxes

[Guest]

Hey gang,
I have a dialogue box that contains checkboxes of all of the worksheets
names in a file. At the moment the list is in a single column....does anyone
know how I can get the checkbox list to be displayed as TWO columns? The list
is too long that I can no longer see the checkboxes at the bottom of the
dialogue box.

Thanks!

 

[tony h]

Some more details on exactly what you are doing would be helpful. There
are varius answers depending on what you want to achieve.

regards,

 

[Guest]

Hi Tony,
Here is the code that I am using. It creates a dialogue box that uses the
worksheets names - I use this box that is created to select specific sheets
for printing.

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 Formulas to print"
End With

I'd just like to be able to have the names/list in two columns instead of
one - the list is too long to be able to see them all, in one column!

Thanks!

 

[tony h]

Sub a()
Dim bIsLeft As Boolean
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
bIsLeft = True
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
If bIsLeft Then
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5

Else
PrintDlg.CheckBoxes.Add 243, TopPos, 150, 16.5
TopPos = TopPos + 13
End If
bIsLeft = Not bIsLeft
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name

End If
Next i


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

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

End Sub


the boolean value bIsLeft only indicates a left or right position. you
could easily change this to a column counter and have several columns.

hope this helps

 

[Guest]

Thanks Tony, I'll give this a try!