'Closing' chart window *and* removing chart using VBA

  • Thread starter Thread starter Marc R. Bertrand
  • Start date Start date
M

Marc R. Bertrand

Hello,

When an embedded chart is in its window, clicking the 'X' of the
window merely removes the window but does not remove the chart (chart
object). Is there a way to remove/delete the chart when clicking on
the 'X'?

In other words, can an an event of some sort that recognizes the
Window becoming invisible, be used delete the chart? Would this sort
of this be solved with Windows handles and APIs...?

Thanks/Cheers.

Marc R. Bertrand
 
I suspect that you can probably do what you want with some amount of
event / Win API programming. However, as pointed out in the response
to the same question from yesterday, a non-programmatic solution
requires a single keystroke -- use of the Delete button.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Marc -

Without blowing up my machine with APIs, I thought some kind of
application or chart event might help.

Application events: there are WindowActivate and WindowDeactivate
events, but the Chart Window is not recognized as a window by the event
handler.

Chart events: the Chart_Activate event fires when you click on the chart
(which you must do before activating the chart window, or non-window),
then nothing when the chart window is opened. When the chart window is
closed (by the X or by clicking somewhere else on the screen), you get a
Chart_Deactivate event followed immediately by a Chart_Activate event.
If you do something clever like Ctrl-Tabbing or selecting another
workbook from the Windows menu, you get a Chart_Deactivate, followed by
the other workbook's window activating, but when you switch back to the
workbook with the chart, you get another Chart_Activate. It seems hard
to me to distinguish between a fatal window close (using the X) and a
benign window close (just clicking somewhere in the worksheet). If you
always want to kill the chart when the chart window is closed, then it
doesn't matter, just set a dummy variable when the Chart_Deactivate
happens, and if it's followed right away by the same chart activating,
then kill the chart.

When the X is clicked (or the chart window closes), the chart is still
selected. Couldn't the user just delete the chart? What is the purpose
of using the chart window to show the chart? If the window is shown in
code, maybe you could at the same time create a dummy command bar with
one button, which when pressed kills the chart, then removes the command
bar.

- Jon
 
I have figured out what I wanted to do. I needed to trap an event for
the chart: the Deactivate event. This works for me. The chart will be
deleted if anything but the chart is clicked. Clicking on the chart
window's 'X' button does it, and so does clicking outside the chart,
ie., a cell.

In a class module that I named clsChartEvents, I have placed the
following:
==========================================================================
Option Explicit
Public WithEvents Embedded_Chart As Chart
Private Sub Embedded_Chart_Deactivate()
ActiveSheet.ChartObjects.Delete
End Sub
==========================================================================
In the regular module that contains the chart making code, I placed
this (the Sub is called from the chart making code):
==========================================================================
Public Click As New clsChartEvents
Sub Enable_chart_events()
Set Click.Embedded_Chart =
ThisWorkbook.Worksheets("Sheet2").ChartObjects(1).Chart
End Sub
==========================================================================



=====================================================================
 
Marc -

This command will remove all charts on the active sheet:

ActiveSheet.ChartObjects.Delete

You can set up your sheet so all charts on it are enabled for chart
events. In the regular code module, put this:

Dim mycharts() As New clsChartEvent

Sub Set_All_Charts()
' Enable events for all charts on a worksheet
If ActiveSheet.ChartObjects.Count > 0 Then
ReDim mycharts(ActiveSheet.ChartObjects.Count)
Dim chtObj As ChartObject
Dim chtnum As Integer

chtnum = 1
For Each chtObj In ActiveSheet.ChartObjects
Set mycharts(chtnum).EmbChart = chtObj.Chart
chtnum = chtnum + 1
Next ' chtObj
End If
End Sub

This enables all charts in the worksheet for events. Then in the
_Deactivate event procedure, you can put this to remove the chart object
that contains it, but leave any others intact:

Embedded_Chart.Parent.Delete

- Jon
 
Jon,

Thanks. I know what ActiveSheet.ChartObjects.Delete does, but I don't
care because in this case, it's only one chart I need to delete. But
thanks again, because the code below is a mighty fine technique that
will come in handy in many a situation.

Since I've got your attention on the subject, perhaps you can save me
some browsing and reading time and tell me how to change the caption
of the chart window. I don't want it to show the usual [book.xls]Sheet
Chartname. I read somewhere that this can't be done until the workbook
is saved again...a pain... Is this true? I want a window because 1)
the chart looks nice and 'finished' in a window and 2) I want to take
advantage of the window caption to display the title of the chart. And
no, I don't want a usual title on the chart that will take up space.
If I can't use the window caption, I guess I will have to reconsider.
This window title will change according to what called the chart
making code...

I look forward to opening that bottle of champagne when you will
announce your new and official Jon Peltier web site :)

Thanks Jon.
 
Marc -

The window caption is easy:

if activewindow.type = xlChartAsWindow then
activewindow.Caption = "Your Message Here"
end if

The if-then makes sure you don't change the workbook or application
window caption.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
Jon,

Thanks. I know what ActiveSheet.ChartObjects.Delete does, but I don't
care because in this case, it's only one chart I need to delete. But
thanks again, because the code below is a mighty fine technique that
will come in handy in many a situation.

Since I've got your attention on the subject, perhaps you can save me
some browsing and reading time and tell me how to change the caption
of the chart window. I don't want it to show the usual [book.xls]Sheet
Chartname. I read somewhere that this can't be done until the workbook
is saved again...a pain... Is this true? I want a window because 1)
the chart looks nice and 'finished' in a window and 2) I want to take
advantage of the window caption to display the title of the chart. And
no, I don't want a usual title on the chart that will take up space.
If I can't use the window caption, I guess I will have to reconsider.
This window title will change according to what called the chart
making code...

I look forward to opening that bottle of champagne when you will
announce your new and official Jon Peltier web site :)

Thanks Jon.

Jon Peltier said:
Marc -

This command will remove all charts on the active sheet:

ActiveSheet.ChartObjects.Delete

You can set up your sheet so all charts on it are enabled for chart
events. In the regular code module, put this:

Dim mycharts() As New clsChartEvent

Sub Set_All_Charts()
' Enable events for all charts on a worksheet
If ActiveSheet.ChartObjects.Count > 0 Then
ReDim mycharts(ActiveSheet.ChartObjects.Count)
Dim chtObj As ChartObject
Dim chtnum As Integer

chtnum = 1
For Each chtObj In ActiveSheet.ChartObjects
Set mycharts(chtnum).EmbChart = chtObj.Chart
chtnum = chtnum + 1
Next ' chtObj
End If
End Sub

This enables all charts in the worksheet for events. Then in the
_Deactivate event procedure, you can put this to remove the chart object
that contains it, but leave any others intact:

Embedded_Chart.Parent.Delete

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Marc R. Bertrand wrote:
Click to expand...
Click to expand...
 
Hello Jon,

Well, here I go again. I solved my chart window title/caption problem
with some information from a previous post and a great book on APIs
that I just bought: by STEVE BROWN: VISUAL BASIC DEVELOPER'S GUIDE TO
THE WIN32 API.

============================================================================
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As
Long
Private Declare Function FindWindowEx Lib "user32" Alias
"FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SetWindowText Lib "user32" _
Alias "SetWindowTextA" (ByVal hwnd As Long, ByVal lpString As
String) As Long
Sub Change_window_chart_caption()
'Gets the handle for the embedded chart: hWndXLE
Dim hWndXL As Long, hWndDesk As Long, hWndXLE As Long
Dim rc As Long
Dim strTitle As String

hWndXL = FindWindow("XLMAIN", Application.Caption)
hWndDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
hWndXLE = FindWindowEx(hWndDesk, 0&, "EXCELE", vbNullString)

strTitle = "THANK YOU MR BULLEN, MR BROWN, AND OTHER PALS..."
rc = SetWindowText(hWndXLE, strTitle)
End Sub

=============================================================================


Jon,

Thanks. I know what ActiveSheet.ChartObjects.Delete does, but I don't
care because in this case, it's only one chart I need to delete. But
thanks again, because the code below is a mighty fine technique that
will come in handy in many a situation.

Since I've got your attention on the subject, perhaps you can save me
some browsing and reading time and tell me how to change the caption
of the chart window. I don't want it to show the usual [book.xls]Sheet
Chartname. I read somewhere that this can't be done until the workbook
is saved again...a pain... Is this true? I want a window because 1)
the chart looks nice and 'finished' in a window and 2) I want to take
advantage of the window caption to display the title of the chart. And
no, I don't want a usual title on the chart that will take up space.
If I can't use the window caption, I guess I will have to reconsider.
This window title will change according to what called the chart
making code...

I look forward to opening that bottle of champagne when you will
announce your new and official Jon Peltier web site :)

Thanks Jon.

Jon Peltier said:
Marc -

This command will remove all charts on the active sheet:

ActiveSheet.ChartObjects.Delete

You can set up your sheet so all charts on it are enabled for chart
events. In the regular code module, put this:

Dim mycharts() As New clsChartEvent

Sub Set_All_Charts()
' Enable events for all charts on a worksheet
If ActiveSheet.ChartObjects.Count > 0 Then
ReDim mycharts(ActiveSheet.ChartObjects.Count)
Dim chtObj As ChartObject
Dim chtnum As Integer

chtnum = 1
For Each chtObj In ActiveSheet.ChartObjects
Set mycharts(chtnum).EmbChart = chtObj.Chart
chtnum = chtnum + 1
Next ' chtObj
End If
End Sub

This enables all charts in the worksheet for events. Then in the
_Deactivate event procedure, you can put this to remove the chart object
that contains it, but leave any others intact:

Embedded_Chart.Parent.Delete

- Jon
Click to expand...
Click to expand...
 
Back
Top