Solved: Preferred Way to Deselect a Chart

[Cyberdude]

I?ve been writing a lot of chart modification VBA utilities lately, and in most I want to deselect the chart just before I exit, and in a few I want to deselect any residual chart selection upon entry to the macro. I?ve had trouble coming up with what I think is a preferred method to do chart deselection. So I wondered what method YOU prefer?

To review, the Excel macro code generator always uses the following sequence:
ActiveWindow.Visible = False
Windows("MyWorkbookName.xls").Activate John Walkenbach recommends:
If Not ActiveChart Is Nothing Then ActiveChart.Deselect which deselects a chart if one is currently selected. This works for embedded charts or on chart sheets.
I?ve found that a simple ?Range ? Select? statement will generally do the trick for embedded charts. Since I like the idea of positioning the cursor somewhere obvious after the deselection, I?ve been using the Walkenbach statement followed by a range select statement:
If Not ActiveChart Is Nothing Then ActiveChart.Deselect
Range(PositionAddr).Select I?ve had problems with using the ?ActiveWindow.Visible = False? at times. I haven?t isolated what causes it, but under certain conditions I wind up making my Windows screen invisible, which is a disturbing event when it happens.

Since I?m still learning, I wondered what the chart gurus have to say on this subject. Do you have still another technique that you prefer? :sleuth:

[Cyberdude]

Hmmmm ... no opinions on this topic. OK, I'll mark it Solved.

[Norie]

Cyberdude

My preferred method would be not to select the chart in the first place.

[Cyberdude]

Norie, I've written a LOT of macros to do special processing of charts, and I don't see how you can avoid selecting a chart. It's just the way the available statements work in VBA. You have to select the chart in order for VBA to know which one you want to modify. I am probably using the word "select" too loosely. Charts do get selected without using the ".Select" explicitly.

P.S. Thanx for the reply.

[Norie]

Cyberdude

I've written a lot of code, and I've seldom seen a situation where selecting/activating is actually required, unless say it involved something like moving to a specific position in a worksheet on start up.

ie the specific objective of the code is to select/activate

[JonPeltier]

I've written a LOT of macros to do special processing of charts, and I don't see how you can avoid selecting a chart. It's just the way the available statements work in VBA. You have to select the chart in order for VBA to know which one you want to modify.
You can refer to the chart using
Worksheets("Book1.xls").Worksheets("Sheet1").ChartObjects("1").Chart
No need to even activate the parent workbook. Any examples of problems you've had beyond this? Statements you couldn't get to work properly?

I?ve had problems with using the ?ActiveWindow.Visible = False? at times. I haven?t isolated what causes it, but under certain conditions I wind up making my Windows screen invisible, which is a disturbing event when it happens.
You should check that a chart is active before setting ActiveWindow.Visible to False.

Hmmmm ... no opinions on this topic. OK, I'll mark it Solved.
Not a good reason to mark it solved.

[Cyberdude]

Jon, thanks for your comments.

You can refer to the chart using



VBA:

Worksheets("Book1.xls").Worksheets("Sheet1").ChartObjects("1").Chart

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)

My biggest problem is how to identify the chart I want to work on. In the statement you posted, it all comes down to the ChartObjects subscript ("1"). If I have a sheet with 5 embedded charts, how do I determine programatically what the subscript is for the chart I want to operate on? I know how to do it if the chart has a title, but if it doesn't??

VBA Help shows the following example:
With Charts("Chrt 4")
.HasTitle = True
.ChartTitle.Text = "First Quarter Sales"
End With In this example, they identify the chart with the string in red. What is that? It's not the title obviously.

And how do I make use of the chart's name? For example, the statement:
ChrtNm = ActiveSheet.ChartObjects(ChrtIdxNo).Chart.Name will return something that has the format:
"SheetName Chart 286"
Can I use this somewhere to identify a chart?

Usually I get around this identification problem in my chart-modification utilities my manually selecting the chart by clicking on it before I execute the utility. But that seems klutzy.

[mdmackillop]

How about a cell reference.

[JonPeltier]

Cyberdude -

I've posted a page about chart names on my web site:

http://peltiertech.com/Excel/ChartsHowTo/NameAChart.html

Basically, the name or index of the chart object is the important one:

ActiveSheet.ChartObjects(1)
ActiveSheet.ChartObjects("Chart 1")

If the charts are created in code, you'd be able to give them a more descriptive name:

ActiveSheet.ChartObjects("cht_MSFT_Price_2006")

The chart also has a name, as you've discovered, but it's not so helpful.

One unfortunate thing about this is if one sheet with charts is copied, and the chartobjects had default names, the names of the chartobjects on the copied sheet may not match those on the original sheet. But you can loop through the chartobjects, looking for the one with identifying attributes (title, or axis title, or category labels you can identify, or whatever). Or, right, you can select the chart and let the code work on the active chart; sometimes there's no getting around that.

Here's an idea: a shape has a top left cell property. Select the cell under the top left corner of the chart, then loop through the chart objects, and find the chart object whose shape has a top left cell that matches the activecell:

For Each chtob In ActiveSheet.ChartObjects
If ActiveSheet.Shapes(chtob.name).TopLeftCell.Address = _
ActiveCell.Address Then
Set TheChartIWant = chtob.Chart
Exit For
End If
Next
If Not TheChartIWant Is Nothing Then
' do your stuff
End If

People have gotten out of the habit of posting their actual solution in the threads (no offense, mdmackillop). Posting the workbook is easier, but I find it breaks up the flow of the discussion to have to download the file, open it, and dig through to find the relevant bits.

[mdmackillop]

If I'd known of the TopLeftCell address property, I wouldn't have bothered with an example in the first place! :bow:

[JonPeltier]

That's why we keep reading these forums then, isn't it?

Oh, and a better algorithm might just be:
If Not Intersect(ActiveSheet.Range(ActiveSheet.Shapes(chtob.name).TopLeftCell, _
ActiveSheet.Shapes(chtob.name).BottomRightCell), _
ActiveCell) Is Nothing Then
so the selected cell can be anywhere under the chart object.

[Cyberdude]

Jon and Malcom, what would I do without you two?

Jon, I have printed a number of your articles, and will begin immediately to study them. Thanks for the references too.

I have already discovered the ability to identify a chart by the top left cell. The only problem with that is that one must be absolutely sure the chart is positioned correctly. A chart shift a little too much to the left can foul up the technique. But I DO use it to advantage.

Another caution has to do with the ChartObjects index number, as opposed to the chart name number. The ChartObjects index for any chart can change if you delete one of the charts. These numbers are from 1 to the maximum number of charts. If there are initially 5 charts, then the index numbers go from 1 to 5. If chart number 3 is deleted, then the number goes from 1 to 4. Then, what was chart number 4 becomes chart number 3, and chart 5 becomes chart 4. This can throw your logic off if you aren't aware of it. The number in the chart name never changes. If you delete a chart, then that number disappears forever, but the other chart names are not affected.

If you create 5 charts in a horizontal line (visually), the the ChartObject index numbers assigned will be 1 to 5 with the leftmost chart being 1 and the righmost chart being number 5. However, you can throw this number scheme off by moving chart 4 to the position occupied by chart 2, and the ChartObject index numbers do not change. However the new order will be 1, 4, 2, 3, 5. Said differently, the chart that becomes the second chart visually will not be ChartObject index 2 ... it will be 4. So visual selection of the index number is hazardous.

Anyway, thanks again for your time in explaining this stuff to me. :friends:

[JonPeltier]

The chart object index also changes if you change the Z order of the charts (e.g., by using send to back, bring to front, etc.).

The trick in working repetitively with the same charts is to assign a descriptive name to each chart object (as I described in my article which I cited above). All you need then is an error trap in case the chart was deleted; otherwise the code should always find the one you intended

[Cyberdude]

Jon, it would seem that a user-assigned chart name might be the one constant that you could depend on. Well, maybe not. When I did some tests, I found that the user-assigned chart name is not required to be unique. The system probably won't change it, but it's possible to have two charts with the same user-assigned name. I suppose you just have to keep good records. It seems like the use of chart titles might have some benefit as well, except that anyone can change a title with not much effort. And you can't use a title in a VBA statement to qualify which chart you want. I have a macro that does make use of titles for identification, but I don't feel it's safe for general use.

[Norie]

Are you creating these charts using VBA?

If you are then why not just create a reference to them when you create them?

[JonPeltier]

Cyberdude -

I think I remember a problem with non-unique chart names. That complicates matters.

You can use a chart title to identify a chart, the same as looping to see which chart covers a given cell.
For Each chtob In ActiveSheet.ChartObjects
If chtob.Chart.ChartTitle.Text = "MyTitle" Then
Set TheChartIWant = chtob.Chart
Exit For
End If
Next
This is subject to a user maintaining consistent chart titles.

[JonPeltier]

Are you creating these charts using VBA?

If you are then why not just create a reference to them when you create them?
The original issue was running a macro on a particular chart in a worksheet, and somehow knowing which chart the user wanted changed. The code which created the chart would no longer be running, so any chart variable would be destroyed.