Chart deselect question - Before Print

[James]

Ive got a sheet with 4 graphs on it and some header information. If a chart
or part of the chart is selected (ie. the trendline) then it only prints that
chart. I need to print the entire worksheet. Ive tried
"Activechart.Deselect" and Activesheet.Range("A1").Select placed in the
Before_Print event with little sucess. Is there another solution to this?
Thanks (very new to chart programming).

 

[Andy Pope]

Hi,

Looks like Excel has already decided what to print by the time this event
fires. So deselecting or activating a cell is too late for printing.
You can warn the user and cancel print if required.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

If TypeName(Selection) <> "Range" Then
If MsgBox("Sure you want to proceed", vbExclamation Or vbYesNo,
"Sheet not active") = vbNo Then
Cancel = True
End If
End If

End Sub

An alternative is to capture the Print or PrintPreview command button being
pressed. This is a bit more complicated.

Class module, Class1
'---------------------------------------------------
Private WithEvents m_cbtPreview As CommandBarButton
Private WithEvents m_cbtPrint As CommandBarButton

Private Sub Class_Initialize()
Set m_cbtPreview = Application.CommandBars.FindControl(ID:=109)
Set m_cbtPrint = Application.CommandBars.FindControl(ID:=2521)
End Sub
Private Sub m_cbtPreview_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)
If TypeName(Selection) <> "Range" Then
ActiveSheet.Range("A1").Select
End If
End Sub

Private Sub m_cbtPrint_Click(ByVal Ctrl As Office.CommandBarButton,
CancelDefault As Boolean)
If TypeName(Selection) <> "Range" Then
ActiveSheet.Range("A1").Select
End If
End Sub
'---------------------------------------------------

Standard Code Module,
'---------------------------------------------------
' Declaration only
Public g_clsPrint As Class1
Public g_clsPrintPreview As Class1
'---------------------------------------------------

Thisworkbook object
'---------------------------------------------------
Private Sub Workbook_Open()
Set g_clsPrint = New Class1
Set g_clsPrintPreview = New Class1
End Sub
'---------------------------------------------------

Cheers
Andy