Define Chart Name???

H

hce

Hi

I have say 5 charts on a worksheet and 5 buttons on another workshee
with each button assigned a different macro. The macro will bring th
user to its respective chart.

I am using a very simple and rudimentary way of doing this... I gave
number to each chart, putting the number behind the chart so that n
one can see it... The macro will then "search" for this number an
scroll the screen to the area where the number is hence showing th
chart...

The problem with this is that it really depends on the user's scree
whether the chart is fully displayed... Hence my question is whethe
there is a better way to do this? Is there a way we can "name" th
charts like how we can name combo box...? Or can we define a name fo
charts...?

I would really appreciate any help/suggestion...

Cheer
 
A

AlfD

Hi!
Is there any particular reason why the 5 charts are on the sam
worksheet? You appear to be viewing them as though the other 4 don'
matter.

I ask this because it would be simple to address charts on differen
sheets.

Al
 
D

Debra Dalgleish

Each chart object has a name automatically assigned when it is created.

To see the chart object's name, hold the Ctrl key, and click on the
chart to select it. The name appears in the Name box, to the left of the
formula bar.

While the chart object is selected, you can rename it, by typing a new
name in the Name box.
 
H

hce

Hi Alf

The charts were placed on the same worksheet to reduce the number o
worksheets. There are in total about 70 charts.

Cheers

Hi Debra

Thank you for your advice. Could you or Alf kindly advice me on how t
write a code in which it will scroll the display to the chart based o
its name? I tried to do something like this based on what I did for th
Userform (Userform1.Show) but apparently, charts don't work like thi
right?

Cheer
 
D

Debra Dalgleish

The following code scroll the first chart into view:

'===================================
Sub ScrollToChart()
On Error Resume Next
Dim ws As Worksheet
Set ws = Sheets("AllCharts")
ws.Activate
ws.Range("A1").Activate
Dim ch As ChartObject
Set ch = Sheets("AllCharts").ChartObjects(1)
With ActiveWindow
.ScrollColumn = ch.TopLeftCell.Column
.ScrollRow = ch.TopLeftCell.Row
End With
End Sub
'==================================
 
H

hce

HI Debra

I went to your website and it was amazing with the tips... I was
looking in particular the pivot tables - to hide/show fields codes...
When I tried your code in my file, the fields were still not visible...
I had to click on the checkbox or the field list to make them show
all...

I am using Excel 2003...Is that the reason why? Would you also by any
chance know how I can turn on/off the "Show All" by code? I would
really appreciate your advice here...

Cheers
 
H

hce

Hi Debra

I tried your revised code... and it worked... almost... The reason wh
I say almost is because for some strange reason, only 2 boxes were lef
unchecked... One being the "Show All" and the other being one of th
fields... Do you know what is the reason for this? Is it because of th
"Show All"?

Would you by any chance know how to check the "Show All" because if yo
do, then the code would just be to check "Show All" and all fields woul
be shown right? However, this would only work in the newer versions a
the older ones do not have the "Show All" function...

In addition, is it possible to check all the fields except for blanks


Hope my input has been useful and I really really appreciate your hel
and advice provided so far...

Cheer
 
H

hce

Hi Debra

Another issue which I have noticed... I tried to use variations of you
code such as Columnfields, Rowfields, Pagefields but they all did no
work properly...

I wasn't able to get all the row or column fields to show... And th
other thing which I'm not very sure is whether your code has catered t
showing all the page field items because I wasn't able to get tha
done...

Would you have any idea on what I did wrong here...?

Cheer
 
D

Debra Dalgleish

At least one of the items in a field will always be visible. If you
don't want any of the items visible, you can remove the field from the
pivot table.

And AFAIK, there's no programming equivalent of checking the "Show All"
button, except to loop through the items in the field.

The following code will hide all the (blank) items in the pivot tables:

'============================
Sub ShowPivotItemsNotBlank()
'hide all pivot items in all tables on sheet
'except for (blank)
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Value = "(blank)" Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pf
pf.AutoSort xlAscending, pf.SourceName
Next pt

End Sub
'================================
 
Top