toggling a chart on and off

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VILLABILLA - 26 Feb 2004 07:04 GMT

Hello!

I use Excel 2000.
I have a macro that creates a chart. I assigned this macro to a
autoshape. When I am done looking at the chart I have to delete it
Isn't there a way to make things in such a way that I could just toggl
the chart to appear and dissapear? For example with an additional macr
or toggle button or something...

This is how the macro looks like:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25.2.2004 by Registered User
'

'
Range("B3:J3,B5:J5").Select
Range("B5").Activate
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceDat
Source:=Sheets("Sheet1").Range("B3:J3,B5:J5"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Target"""
ActiveChart.SeriesCollection(2).Name = "=""Actuals"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "T vs A"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWindow.Visible = False
Windows("try out.xls").Activate
Range("A3").Select
End Sub

Every idea is welcome!

p.s.

I am not familiar with VBE language so please explain like to a 4 yea
old;)

Thanks a lot!

Robb

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Tushar Mehta - 27 Feb 2004 01:27 GMT

I have to ask why? Isn't it the easiest, simplest, and quickest to
select the chart and press the delete button?

That said, you could adapt

activesheet.chartobjects(1).visible=false

to your own needs through another macro assigned to another XL object.

Personally, anytime I find myself adding code to do something that is
incredibly easy to do through the GUI, I pause and usually rationalize
my way out.

Signature

Regards,

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

> Hello!
>
[quoted text clipped - 48 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

VILLABILLA - 27 Feb 2004 07:13 GMT

Thank you very much for your solution. My spreadsheet is full of littl
charts that only need a quick view and need to be closed after that
that's why it would be easier to make them dissapear with a press on
button. So I tried your suggestion that is shown below. Unfortunatel
it works only the first time when I added the autoshape with th
assigned macro. After it deleted a chart one time it doesn't want to d
it after that anymore...

What am I doing wrong?

Sub delchart()
'
' delchart Macro
' Macro recorded 27.2.2004 by Registered User
'
ActiveSheet.ChartObjects(1).Visible = False
'
End Sub

Thanks very much!

Robb

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Tushar Mehta - 27 Feb 2004 16:30 GMT

You need to decide what you want to do. Deleting an object is
different from hiding it. Also, you need to provide more details: what
does it mean when you write "it doesn't want to do it after that
anymore...?" What doesn't want to do what to whom when?

Note that deleting the object requires one keystroke. Why would anyone
believe that is is better to click a button and run a macro that mimics
pressing the Delete button is not clear to me.

If you hide an object, it is still on the worksheet using up resources.
It just is no longer visible. If you keep on 'deleting' objects by
hiding them and creating new objects as required, sooner or later you
will run into all sorts of resource problems.

Signature

Regards,

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

> Thank you very much for your solution. My spreadsheet is full of little
> charts that only need a quick view and need to be closed after that,
[quoted text clipped - 21 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

VILLABILLA - 28 Feb 2004 09:28 GMT

Thank you for your reply, sorry if I was unclear, I ment that the macr
you gave me only takes out the first chart on my sheet, after that i
doesn't work anymore. Without a doubt a handy solution but not for m
issue... Yestersay I received this macro:

Dim ch As ChartObject

For Each ch In ActiveSheet.ChartObjects
ch.Delete
Next ch

It deletes the charts, not hides them, or am I wrong?
It atleast seems to work very fine...

If it would be as easy to just use the delete button then I wouldn'
raise this issue. The spreadsheet will be used pretty intensively b
several users, so I want it to be as user friedly as possible. I don'
want to select chart by chart and delete them if I can also just delet
them with a click on a button.

Kindest regards

--
Message posted from http://www.ExcelForum.com

Reply to this Message