customizing the mouse-over display in a bubble chart

[Mark]

Hi --
I have a little Excel app that generates 3dbubble charts from data stored in
one sheet of my workbook, based on a user's selections. The DB portion of
this app contains info about a set of possible projects that might be done,
including risk, reward, cost, date of avaliability etc. Of course, every
project has a name and belongs to one of several product families.

When the user determines what elements of the project he wants to use to
determine the x and y coordinates of the bubble and it's radius, I construct
the chart in 'family' series, so all the projects for a given family all
have the
same color. Thus - each bubble shows 4 elements - x, y, rad, and color
(family).

Once generated, the chart is cool... but when you mouse-over a given bubble,
it tells you what you can readily determine from the chart - the
series(family) name, the x and y coords of the center, and the radius. Duh!
that's totally
derivitive. What I want to see in the mouse over is the 'fifth' relevant
item of info - the name of the specific project.

So. My questions is -- Is there a way to alter/customize the mouse-over
content for each bubble to display a text string of my choosing?

Thanks in advance for your help,
Mark

[Haydar]

dear mark

do i understand you correctly that you have a tool that draws bubbles in a sheet?
my problem is that i can�t use the bubble chart because it needs a certain formatted sheet with x y and size.
i am desparetly searching for a solution and it would be great if you could send me the macro or tool or just give be a hint!

thanks a lot

best regards
haydar

[Mark]

Hi Haydar --

Tried sending you email but it bounced (address:
'[email protected]').
I have a sample spreadsheet to send you if you let me know where to send it.

Here's the text of the message I tried to send:


Hi Haydar





In order to generate a 3d bubble chart, you need to provide 3 data elements
per bubble: the X and Y coordinates of the center of the bubble, and the
diameter.



If you want, you can add a 4th element (color), by specifying more than one
series of cells as you build the chart. In this case, Color tells me to which
‘Family’ a given bubble belongs.



I’m attaching a spreadsheet with data and a chart to illustrate. I’m also
appending a code segment to this message. To use the code, you need to drop
it into a VB macro or sub(procedure).



There’s several hundred lines of code in the Excel app preceding the code
segment below, so some of it may not make sense. I’ll try to give you an
overview:



Basically, I’ve got a DB of about 50 columns by up to 200 rows. Each row
represents an investment opportunity. The Columns contain info related to
each opportunity – risk, reward, cost, ‘family’, projected date of
availability …



The bubble chart portion of the app includes a form which allows the user to
specify several search criteria (delivery date, industry, …), and what values
for each of the opportunities that fit the criteria should be used for X, Y
and Diam. When that’s done, he/she clicks on a button and the app pulls the
relevant info out of the DB and plops it into a new worksheet. As it does so,
it drops the data into the sheet in ‘family’ order, remembering where the
start & end rows are for each family.



In the code snippet below, the filtered & selected data has already been
plopped into a (recently cleared) sheet (“BubbleData”) in the workbook. I’ve
done a global replace on one of my variable names to turn it into
“DataSeries” (think “Family”). Since I don’t know how many Families there
may be in the DB (as time goes by new families of investments may get added),
I have to wander through the db and populate a list of family names and count
how many there are… so there’s a DataSeriesCount (number of
DataSeries/Families found in the DB), a DataSeriesIndex, etc. I also populate
some strings for titles and family names, and for every DataSeries there’s a
string containing the Start of its range and the end of it’s range (in the
sheet “BubbleData”). I tried passing those values as ranges and couldn’t
make it work, but passing them inside a string worked.



Disclaimer: I’m new to VB (this is my first VB app) so there may be
easier/more elegant ways to do what I’ve done here… but I got this to work,
so I stopped ‘improving’ it... Except I would like to be able to see the
specific name of the opportunity when I mouse over the resulting bubble,
instead of the X, Y and Diam values… Haven’t figured out how to do that yet.





In the code snippet below, I have previously determined the average x and y
values ( I want the axes to bisect the chart – show me 4 quadrants). And I
have title and Axis strings that let the user see what data fields were used
for X, Y and Diam, as well as other search criteria (filters).



Let me know if this helps. Or not J



Mark Hinrichs





Here’s the VB code:



----------------------------------------------------- cut here
-------------------------------------------------------------------------------





' Build Chart --- Data comes from sheet “BubbleData”, and Chart gets dumped
into sheet “BubbleChart”



Charts.Add

ActiveChart.ChartType = xlBubble3DEffect

ActiveChart.SetSourceData Source:=Sheets("BubbleData").Range( _

DataSeriesChartRangeStr(1)), PlotBy:=xlColumns



For DataSeriesIndex = 2 To DataSeriesCount



ActiveChart.SeriesCollection.NewSeries ' Add all new series
before defining ranges & titles



Next DataSeriesIndex

DataSeriesNameStr = DataSeriesName(1)

ActiveChart.SeriesCollection(1).Name = DataSeriesNameStr



For DataSeriesIndex = 2 To DataSeriesCount



DataSeriesNameStr = DataSeriesName(DataSeriesIndex)

ChartRangeStartRowStr = DataSeriesStartRow(DataSeriesIndex) ' cast
integer vals into strings

ChartRangeEndRowStr = DataSeriesEndRow(DataSeriesIndex)



XValsStr = "=BubbleData!R" + ChartRangeStartRowStr + "C2:R" +
ChartRangeEndRowStr + "C2"

YValsStr = "=BubbleData!R" + ChartRangeStartRowStr + "C3:R" +
ChartRangeEndRowStr + "C3"

BubbleSizeStr = "=BubbleData!R" + ChartRangeStartRowStr + "C4:R" +
ChartRangeEndRowStr + "C4"



ActiveChart.SeriesCollection(DataSeriesIndex).XValues = XValsStr



ActiveChart.SeriesCollection(DataSeriesIndex).Name = DataSeriesNameStr

ActiveChart.SeriesCollection(DataSeriesIndex).BubbleSizes =
BubbleSizeStr

ActiveChart.SeriesCollection(DataSeriesIndex).Values = YValsStr



Next DataSeriesIndex



ActiveChart.Location Where:=xlLocationAsObject, Name:="BubbleChart"

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = TitleStr

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XaxisStr

.Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YaxisStr

End With

With ActiveChart

.HasAxis(xlCategory, xlPrimary) = True

.HasAxis(xlValue, xlPrimary) = True

End With

ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic

ActiveChart.Axes(xlValue).Select

With ActiveChart.Axes(xlValue)

.MinimumScaleIsAuto = True

.MaximumScaleIsAuto = True

.MinorUnitIsAuto = True

.MajorUnitIsAuto = True

.Crosses = xlCustom

.CrossesAt = YAxisAvg

.ReversePlotOrder = False

.ScaleType = xlLinear

.DisplayUnit = xlNone

End With

ActiveChart.Axes(xlCategory).Select

With ActiveChart.Axes(xlCategory)

.MinimumScaleIsAuto = True

.MaximumScaleIsAuto = True

.MinorUnitIsAuto = True

.MajorUnitIsAuto = True

.Crosses = xlCustom

.CrossesAt = XAxisAvg

.ReversePlotOrder = False

.ScaleType = xlLinear

.DisplayUnit = xlNone

End With



Set ThisChart =
ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count) ' Scale the chart
to consume the region A1 to P37

With Range("A1:P37")

ThisChart.Left = .Left

ThisChart.Top = .Top

ThisChart.Width = .Width

ThisChart.Height = .Height

End With





"Haydar" wrote:

>
> dear mark
>
> do i understand you correctly that you have a tool that draws bubbles
> in a sheet?
> my problem is that i can´t use the bubble chart because it needs a
> certain formatted sheet with x y and size.
> i am desparetly searching for a solution and it would be great if you
> could send me the macro or tool or just give be a hint!
>
> thanks a lot
>
> best regards
> haydar
>
>
> --
> Haydar
> ------------------------------------------------------------------------
> Haydar's Profile: http://www.excelforum.com/member.php...o&userid=26041
> View this thread: http://www.excelforum.com/showthread...hreadid=380732
>
>