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!

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

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

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

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