Excel 2002: A workbook I'm building has 3 worksheets that each have a
chart on the left, and some embedded controls to the right that
provide information and manipulation of the chart and its underlying
data.

On the DeNorm worksheet (one of the 3), at the bottom of the control
area, I placed two linked OptionButtons called obPrint1 and obPrint2,
and a CommandButton called PrintButton. The option buttons are
labeled "Print Plot Only (Portrait)" and "Print All (Landscape)". The
PrintButton_Click event module calls the PrintPage sub, which is in a
standard module. It is reproduced below. This all works just fine.

Now here's the problem. As long as this setup exists only on the
DeNorm worksheet, everything is good. Then I install a parallel set
of OptionButtons and CommandButton on another of the 3 worksheets.
The controls have the same names as on the DeNorm worksheet, and the
PrintButton_Click event module again calls PrintPage. After I make
the changes, while the workbook is open, the new controls do what
they're supposed to. Then I save the workbook and open it again, and
I get this: "Microsoft Excel has encountered a problem and needs to
close. We are sorry for the inconvenience". When I check the option
to attempt a recovery, all the VBA code is stripped out of the
workbook. This is a reproducible occurrence.

So who made the error: me or Excel? And if it's me, WHAT is the
error? I've tried cleaning the code - no help. I've tried disabling
macros on startup, and the same termination occurs anyway.

Thanks,
Ken Dahlberg

Public Sub PrintPage()
With ActiveSheet.PageSetup
If ActiveSheet.obPrint1 Then
.Orientation = xlPortrait
.Zoom = 100
Else
.Orientation = xlLandscape
.Zoom = 85
.PrintArea = "$A$1:$Q$43"
End If
End With
If ActiveSheet.obPrint1 Then
ActiveSheet.ChartObjects(1).Chart.PrintOut To:=1
Else
ActiveSheet.PrintOut To:=1
End If
ActiveSheet.DisplayPageBreaks = False
End Sub

1. This might just be a case of "lazy programming". Try :-
If ActiveSheet.ObPrint1.Value = True Then ...............

2. You are in a dodgy position using controls from the Controls
Toolbox instead of the Forms Toolbox. The Controls toolbox set is very
bug ridden and can give big problems - especially if using several. If
1. fails, this might be your problem.

3. I cannot see why you need option buttons anyway. Why not 2 ordinary
buttons (from 'Forms'), one for each method of printing with its own
code. Much simpler. One mouse click instead of 2 to run.


Regards
BrianB
----------------------------------------------------------



(Ken Dahlberg) wrote in message . com...
Excel 2002: A workbook I'm building has 3 worksheets that each have a
chart on the left, and some embedded controls to the right that
provide information and manipulation of the chart and its underlying
data.

On the DeNorm worksheet (one of the 3), at the bottom of the control
area, I placed two linked OptionButtons called obPrint1 and obPrint2,
and a CommandButton called PrintButton. The option buttons are
labeled "Print Plot Only (Portrait)" and "Print All (Landscape)". The
PrintButton_Click event module calls the PrintPage sub, which is in a
standard module. It is reproduced below. This all works just fine.

Now here's the problem. As long as this setup exists only on the
DeNorm worksheet, everything is good. Then I install a parallel set
of OptionButtons and CommandButton on another of the 3 worksheets.
The controls have the same names as on the DeNorm worksheet, and the
PrintButton_Click event module again calls PrintPage. After I make
the changes, while the workbook is open, the new controls do what
they're supposed to. Then I save the workbook and open it again, and
I get this: "Microsoft Excel has encountered a problem and needs to
close. We are sorry for the inconvenience". When I check the option
to attempt a recovery, all the VBA code is stripped out of the
workbook. This is a reproducible occurrence.

So who made the error: me or Excel? And if it's me, WHAT is the
error? I've tried cleaning the code - no help. I've tried disabling
macros on startup, and the same termination occurs anyway.

Thanks,
Ken Dahlberg

Public Sub PrintPage()
With ActiveSheet.PageSetup
If ActiveSheet.obPrint1 Then
.Orientation = xlPortrait
.Zoom = 100
Else
.Orientation = xlLandscape
.Zoom = 85
.PrintArea = "$A$1:$Q$43"
End If
End With
If ActiveSheet.obPrint1 Then
ActiveSheet.ChartObjects(1).Chart.PrintOut To:=1
Else
ActiveSheet.PrintOut To:=1
End If
ActiveSheet.DisplayPageBreaks = False
End Sub