Resize msgbox windows

[bramnizzle]

I have this journaling spreadsheet where the users record the days
activities. A macro saves that days journal as a new sheet. I didn't
want to have to have the user have a new copy of the spreadsheet every
month, so we opted to use it for 3 months and the start a fresh copy.
When the user starts a new day, the previous day sheet is saved and
hidden. The user can pull up previous days entries via another
macro. This macro "Select Sheets" brings up a msgbox with all of the
sheets available to view. The problem with that is when you use 3
months, that's approximately 60 sheets (regular 5 day work week x
3months) and the pop-up window that shows these sheets to unhide is
such that you can't see all of the entries. Is there a way to resize
this msgbox window so that I can see all of my entries? Below is my
code. Thanks.

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.Name <> "Start Here" And _
CurrentSheet.Name <> "Journal" And _
CurrentSheet.Name <> "Copy Info" And _
CurrentSheet.Name <> "Tasks" And _
CurrentSheet.Name <> "Projects" And _
CurrentSheet.Name <> "archive" And _
CurrentSheet.Name <> "1" And _
CurrentSheet.Name <> "2" And _
CurrentSheet.Name <> "Drop Down Menus" Then
' 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 Archived 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.Visible = True
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

 

[Susan]

try a userform instead of a message box.
OR
have the macro insert a new sheet & populate it as a summary sheet
from which the user can pick. after sheet is picked, summary sheet is
deleted........... (could be hidden, too, but with so many sheets
already hidden i didn't think you'd want one more.)
just ideas

susan

 

[Jim Cone]

Maybe instead...
Application.Dialogs(xlDialogWorkbookUnhide).Show
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
I have this journaling spreadsheet where the users record the days
activities. A macro saves that days journal as a new sheet. I didn't
want to have to have the user have a new copy of the spreadsheet every
month, so we opted to use it for 3 months and the start a fresh copy.
When the user starts a new day, the previous day sheet is saved and
hidden. The user can pull up previous days entries via another
macro. This macro "Select Sheets" brings up a msgbox with all of the
sheets available to view. The problem with that is when you use 3
months, that's approximately 60 sheets (regular 5 day work week x
3months) and the pop-up window that shows these sheets to unhide is
such that you can't see all of the entries. Is there a way to resize
this msgbox window so that I can see all of my entries? Below is my
code. Thanks.
-snip-