Home |
Search |
Today's Posts |
#1
|
|||
|
|||
customizing the mouse-over display in a bubble chart
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Hi Haydar --
Tried sending you email but it bounced (address: '). 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. Im attaching a spreadsheet with data and a chart to illustrate. Im also appending a code segment to this message. To use the code, you need to drop it into a VB macro or sub(procedure). Theres several hundred lines of code in the Excel app preceding the code segment below, so some of it may not make sense. Ill try to give you an overview: Basically, Ive 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 thats 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. Ive done a global replace on one of my variable names to turn it into €œDataSeries€ (think €œFamily€). Since I dont 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 theres 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 theres a string containing the Start of its range and the end of its range (in the sheet €œBubbleData€). I tried passing those values as ranges and couldnt make it work, but passing them inside a string worked. Disclaimer: Im new to VB (this is my first VB app) so there may be easier/more elegant ways to do what Ive 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€¦ Havent 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 Heres 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).XVal ues = XValsStr ActiveChart.SeriesCollection(DataSeriesIndex).Name = DataSeriesNameStr ActiveChart.SeriesCollection(DataSeriesIndex).Bubb leSizes = BubbleSizeStr ActiveChart.SeriesCollection(DataSeriesIndex).Valu es = YValsStr Next DataSeriesIndex ActiveChart.Location Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ho to change in the bubble chart the bubble position and size | Charts and Charting in Excel | |||
Mouse continues to select after clicking on a cell in excel. | Excel Discussion (Misc queries) | |||
How do I move a hidden bubble to the front in a bubble chart in E. | Excel Discussion (Misc queries) | |||
moving mouse lighlights cells | Excel Discussion (Misc queries) | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |