Create a menu of sheets in a shared workbook
 

An earlier thread put me on to creating a menu of worksheets that can be
selected for printing by adding a temporary dialog sheet (using
ActiveWorkbook.DialogSheets.Add - the full link is at
http://www.j-walk.com/ss/excel/tips/tip48.htm).

However, this does not appear to be possible if the workbook is shared.
Does any one know if there is a method that can be used in a shared workbook?

Thanks

Create a menu of sheets in a shared workbook
 

Instead of using a dialogsheet in that workbook, maybe creating a new workbook
and adding a dialogsheet there????

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
Dim curWkbk As Workbook

Application.ScreenUpdating = False

Set curWkbk = ActiveWorkbook

' 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 = Workbooks.Add.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To curWkbk.Worksheets.Count
Set CurrentSheet = curWkbk.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 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.PrintOut
' ActiveSheet.PrintPreview 'for debugging
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



RichieRich wrote:

An earlier thread put me on to creating a menu of worksheets that can be
selected for printing by adding a temporary dialog sheet (using
ActiveWorkbook.DialogSheets.Add - the full link is at
http://www.j-walk.com/ss/excel/tips/tip48.htm).

However, this does not appear to be possible if the workbook is shared.
Does any one know if there is a method that can be used in a shared workbook?

Thanks

--

Dave Peterson

Create a menu of sheets in a shared workbook
 

Thanks Dave. Works great. Just one last question. Is there any way to
automatically close (without saving) the work book that gets created?

"Dave Peterson" wrote:

Instead of using a dialogsheet in that workbook, maybe creating a new workbook
and adding a dialogsheet there????

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
Dim curWkbk As Workbook

Application.ScreenUpdating = False

Set curWkbk = ActiveWorkbook

' 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 = Workbooks.Add.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To curWkbk.Worksheets.Count
Set CurrentSheet = curWkbk.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 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.PrintOut
' ActiveSheet.PrintPreview 'for debugging
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



RichieRich wrote:

An earlier thread put me on to creating a menu of worksheets that can be
selected for printing by adding a temporary dialog sheet (using
ActiveWorkbook.DialogSheets.Add - the full link is at
http://www.j-walk.com/ss/excel/tips/tip48.htm).

However, this does not appear to be possible if the workbook is shared.
Does any one know if there is a method that can be used in a shared workbook?

Thanks

--

Dave Peterson

Create a menu of sheets in a shared workbook
 

It would probably be better to replace this portion:

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

with

curwkbk.close savechanges:=false

(I didn't read enough of John's code to notice this portion. Sorry.)

RichieRich wrote:

Thanks Dave. Works great. Just one last question. Is there any way to
automatically close (without saving) the work book that gets created?

"Dave Peterson" wrote:

Instead of using a dialogsheet in that workbook, maybe creating a new workbook
and adding a dialogsheet there????

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
Dim curWkbk As Workbook

Application.ScreenUpdating = False

Set curWkbk = ActiveWorkbook

' 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 = Workbooks.Add.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To curWkbk.Worksheets.Count
Set CurrentSheet = curWkbk.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 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.PrintOut
' ActiveSheet.PrintPreview 'for debugging
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



RichieRich wrote:

An earlier thread put me on to creating a menu of worksheets that can be
selected for printing by adding a temporary dialog sheet (using
ActiveWorkbook.DialogSheets.Add - the full link is at
http://www.j-walk.com/ss/excel/tips/tip48.htm).

However, this does not appear to be possible if the workbook is shared.
Does any one know if there is a method that can be used in a shared workbook?

Thanks

--

Dave Peterson


--

Dave Peterson