msg for ron

[rob]

Ron you posted me the code at the bottom of this post.

Can you tell me which line of code I need to change to
make the outcome to view the sheet instead of print.

thanks

rob

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet
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.Sheets.Count
Set CurrentSheet = ActiveWorkbook.Sheets(i)
' Skip empty sheets and hidden sheets
If 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
Sheets(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

 

[JulieD]

Hi Rob

just in case ron's not around i'll give it a go ....

in his code - you'll see a green line (in your VBE window) that says
' ActiveSheet.PrintPreview 'for debugging

the line prior to this is
ActiveSheet.PrintOut

what you need to do is place an ' before the ActiveSheet.PrintOut and delete
the ' from in front of the 'ActiveSheet.PrintPreview line
e.g.
' ActiveSheet.PrintOut
ActiveSheet.PrintPreview 'for debugging

and this should then give you what you're after.

Cheers
JulieD

 

[Guest]

Do you know how to leave out some sheets you specify in
the code?

I have one called data,
I have one which is called dialog?

by ? i mean a number which gets greater in time a
dialogue is opened before closing the workbook.

thanks again.

 

[Ron de Bruin]

Hi

You can use this in John's code
http://www.j-walk.com/ss/excel/tips/tip48.htm

If CurrentSheet.Visible And CurrentSheet.Name <> "data" _
And Left(CurrentSheet.Name, 6) <> "Dialog" Then