Chart deselect question - Before Print

  • Thread starter Thread starter James
  • Start date Start date
J

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).
 
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
 
Back
Top