Dialogue box check boxes

[Tom]

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,

[Tom]

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" wrote:

>
> Some more details on exactly what you are doing would be helpful. There
> are varius answers depending on what you want to achieve.
>
> regards,
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=504425
>
>

[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

[Tom]

Thanks Tony, I'll give this a try!

"tony h" wrote:

>
> 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
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=504425
>
>