PDA

View Full Version : Chart pop up



hawk77
06-20-2014, 12:40 AM
I have a two sheets: one with charts/graphs and one with tables that show the sums of figures, however I would like to have the graph coming up when I either hover over the cell or click on the cell in the table.

So basically the graph in Sheet 2 corresponding to the figure in the table in Sheet 1 should come up. The report is updated weekly so I managed to build a macro that saves the graph as gif, however I am not sure how i can insert the picture of the graph to pop up when i click on the cell in sheet 1.

Your help would be greatly appreciated,

Aussiebear
06-20-2014, 01:45 AM
Can you set the graph up as a tooltip?

p45cal
06-20-2014, 03:06 AM
I think a sample file is needed.

hawk77
06-22-2014, 06:24 PM
1185311854

For some reason I cant seem to be able to upload files. So I had upload them as pictures.

p45cal
06-22-2014, 11:17 PM
A file is needed. Either share via a file sharing site or get your post count here to 5 or more and you should be able to quote urls and upload files without problems.

hawk77
06-22-2014, 11:45 PM
A file is needed. Either share via a file sharing site or get your post count here to 5 or more and you should be able to quote urls and upload files without problems.

Oh ok, I understand...i was wondering why the heck I can't upload files!

hawk77
06-22-2014, 11:46 PM
It seems rather strange to have quota in order to post files, etc. But I assume is just for security reason!

hawk77
06-22-2014, 11:46 PM
And this will be my 5th post!

hawk77
06-22-2014, 11:57 PM
https://dl.dropboxusercontent.com/u/56871515/MockupGraph.xls

Here is the file.

p45cal
06-23-2014, 02:18 AM
Well confused. You want:
1. a chart to pop up: the whole chart on Sheet1 with all the lines that are on it? Perhaps just one of the lines? There is only one chart on Sheet1 although you refer to charts.
2. when you hover/click on a cell: A cell on which sheet? Which table?

Perhaps give a little more on what you're expecting to happen when a user does something?

There are several ways to do this, you've probably been Googling and found a way using a .gif image; were you intending to use this image on a userform, a label?
Another way is to create a new graph everytime, then delete it, or to have an invisible chart on the sheet, then make it appear/disappear after tweaking what it's charting invisibly.

Sorry there are so many questions but I want to give you an elegant solution with a best fit for you.

Also very important is the version of Excel you're using (I note the file is an xls file, not an xlsx or xlsm file) and do you want it to work in later versions of Excel?

p45cal
06-23-2014, 02:23 AM
Oh, I see you've posted to excelforum too: http://www.excelforum.com/excel-programming-vba-macros/1019061-chart-pop-up.html
If you've cross posted elsewhere could you include links to these other threads too, it's only netiquette.
Have a peep at: http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

p45cal
06-23-2014, 04:18 AM
…while waiting to hear back, have a look at the attached file, click/select any single cell in column B on sheet2.

The code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'remove chart
On Error Resume Next
ActiveSheet.Shapes("PopUpChart").Delete
On Error GoTo 0
If Not Intersect(Target, Columns(2)) Is Nothing Then
'add chart
Set rangetoplot = Sheets("sheet1").Columns("A").Find(Target.Offset(, -1).Value).Resize(, 12)
Set newCht = ActiveSheet.ChartObjects.Add(ActiveCell.Left + ActiveCell.Width + 5, ActiveCell.Top, 250, 130)
With newCht.Chart
.Parent.Name = "PopUpChart"
.Type = 4
.SeriesCollection.Add rangetoplot, 1, True
.Legend.Delete
.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 9
End With
End If
End Sub

hawk77
06-23-2014, 05:46 PM
Oh, I see you've posted to excelforum too/
If you've cross posted elsewhere could you include links to these other threads too, it's only netiquette.



Really appreciated and I apologize but I wasn't getting any answers. Moving forward I will try to keep it neat and post the other forums link.

Thank you for letting me know. ;)

hawk77
06-23-2014, 06:07 PM
Well confused. You want:
1. a chart to pop up: the whole chart on Sheet1 with all the lines that are on it? Perhaps just one of the lines? There is only one chart on Sheet1 although you refer to charts.
2. when you hover/click on a cell: A cell on which sheet? Which table?

Perhaps give a little more on what you're expecting to happen when a user does something?

There are several ways to do this, you've probably been Googling and found a way using a .gif image; were you intending to use this image on a userform, a label?
Another way is to create a new graph everytime, then delete it, or to have an invisible chart on the sheet, then make it appear/disappear after tweaking what it's charting invisibly.

Sorry there are so many questions but I want to give you an elegant solution with a best fit for you.

Also very important is the version of Excel you're using (I note the file is an xls file, not an xlsx or xlsm file) and do you want it to work in later versions of Excel?

Ok, i should have been clearer in my intentions. Here it is.
1. Yes, the chart should have only one line that corresponds to the year - one line for 2013, one for 2014. I have few more tables that will require a chart so that is why I said charts.
2. When I hover over or click on the cell in the table (preferable as it is easier I think) - the table on Sheet2 which shows only the latest figure. On sheet one I have the weekly figures and sheet two has only a table with the latest figures.
3. i am using the latest version (xlsx)
4. The Excel file will be uploaded on a sharepoint website for internal stakeholders to see. It is basically a dashboard and some figures are linked to a pivot table but I am not going to go into that.
The way I was thinking I would like it to look like is when you click on one cell in the Sheet two table, a chart/graph will pop up in a dialogue box like, or somewhere on the side that will show you the weekly progress. I don't mind having a hidden spreadsheet with all the graphs that I can update every week and export them as .jpg using a macro.

I really appreciate your help with this. ;)

p45cal
06-23-2014, 11:40 PM
The way I was thinking I would like it to look like is when you click on one cell in the Sheet two table, a chart/graph will pop up in a dialogue box like, or somewhere on the side that will show you the weekly progress.So does the code in msg#12 fit the bill? Is it something you can use/tweak?

hawk77
06-25-2014, 12:10 AM
So does the code in msg#12 fit the bill? Is it something you can use/tweak?

I used this to export the graph:


Sub ExportChart()
' Export a selected chart as a picture
Const sSlash$ = "/"
Const sPicType$ = ".gif"
Dim sChartName$
Dim sPath$
Dim sBook$
Dim objChart As ChartObject


On Error Resume Next
' Test if there are even any embedded charts on the activesheet
' If not, let the user know
Set objChart = ActiveSheet.ChartObjects(1)
If objChart Is Nothing Then
MsgBox "No charts have been detected on this sheet", 0
Exit Sub
End If


' Test if there is a single chart selected
If ActiveChart Is Nothing Then
MsgBox "You must select a single chart for exporting ", 0
Exit Sub
End If


Start:
sChartName = Application.InputBox("Please Specify a name for the exported chart" & vbCr & _
"There is no default name available" & vbCr & _
"The chart will be saved in the same folder as this file", "Chart Export", "")

' User presses "OK" without entering a name
If sChartName = Empty Then
MsgBox "You have not entered a name for this chart", , "Invalid Entry"
GoTo Start
End If

' Test for Cancel button
If sChartName = "False" Then
Exit Sub
End If

' If a name was given, chart is exported as a picture in the same
' folder location as their current file
sBook = ActiveWorkbook.Path
sPath = sBook & sSlash & sChartName & sPicType
ActiveChart.Export Filename:=sPath, FilterName:="GIF"

End Sub

hawk77
06-25-2014, 12:16 AM
…while waiting to hear back, have a look at the attached file, click/select any single cell in column B on sheet2.

The code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'remove chart
On Error Resume Next
ActiveSheet.Shapes("PopUpChart").Delete
On Error GoTo 0
If Not Intersect(Target, Columns(2)) Is Nothing Then
'add chart
Set rangetoplot = Sheets("sheet1").Columns("A").Find(Target.Offset(, -1).Value).Resize(, 12)
Set newCht = ActiveSheet.ChartObjects.Add(ActiveCell.Left + ActiveCell.Width + 5, ActiveCell.Top, 250, 130)
With newCht.Chart
.Parent.Name = "PopUpChart"
.Type = 4
.SeriesCollection.Add rangetoplot, 1, True
.Legend.Delete
.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 9
End With
End If
End Sub

This works but I am not sure how you select the range on the first sheet and assign the graph to the B column on sheet two.

p45cal
06-25-2014, 04:52 AM
This works but I am not sure how you select the range on the first sheet…With the line:
Set rangetoplot = Sheets("sheet1").Columns("A").Find(Target.Offset(, -1).Value).Resize(, 12)
rangetoplot is a variable, being the range of values to plot on the chart. The .Find method searches in Sheets("sheet1").Columns("A") for Target.Offset(, -1).Value)
Target is the cell (a range) which is the cell you've selected (a cell in column(2) aka column B) on Sheet2. The Offset(, -1) is the cell directly to the left, the cell containing the year.
So the line searches column A of Sheet1 for the contents of the cell directly to the left of the cell you've selected in column B of Sheet2.
Once found, the .Resize(, 12) just returns that found cell and extends it to the right so that the range is now 12 cells wide and includes the year data (as a row header) in column A
[For more on this do a search in the vb editor's Help for range.find]


While debugging the code, you can add some lines to confirm what's what while you step through the code line by line by pressing F8 repeatedly.
A line such as:
Application.Goto rangetoplot
will select the rangetoplot range, changing the active sheet if necessary. If you do add such a temporary line, because it does change the active sheet, you need to re-activate the sheet you were on by following it with a temporary:
me.activate
straight after.

Setting rangetoplot is all well and good, but it needs to be used in the chart and that's achieved in the line:
.SeriesCollection.Add rangetoplot, 1, True
[For more on this do a search in the vb editor's Help for SeriesCollection.Add]

I'll include code below with the added lines commented-out so you can see what's going on by commenting them in and stepping through the code. That code will also incorporate the little changes mentioned above and more comments. If you put a breakpoint in the first line of code (the one beginning Private Sub Worksheet_Sel…), as soon as you make a selection on the sheet you should be presented with the macro and a yellow highlight showing where you are in the code, now pres F8 to run through it line by line.
There are also some other changes where I've replaced ActiveSheet with Me, to make it more robust and resilient to which sheet is the active sheet.



…and assign the graph to the B column on sheet two.That's done by the line:
Set newCht = ActiveSheet.ChartObjects.Add(ActiveCell.Left + ActiveCell.Width + 5, ActiveCell.Top, 250, 130)
which I have changed to:
Set newCht = Me.ChartObjects.Add(Target.Left + Target.Width + 5, Target.Top, 250, 130)
The first argument (Target.Left + Target.Width + 5) is where the left hand edge of the new chart will be placed. Target is the cell you've selected in column B. Target.Left is the position on the sheet of the left edge of that cell, I add to it Target.Width, being the width of that selected cell, then I add another 5 to take it a little further to the right of that cell.
The second argument (Target.Top) is where the top of the new chart will be placed, being the top of the selected cell.
The third argument (250) is the width of the new chart.
The fourth argument (130) is the height of the new chart.
[For more on this do a search in the vb editor's Help for ChartObjects.Add]

There's more to add to make it robust, for example, if the year isn't found on the other sheet (at the moment, it will just crash).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'remove chart, so that the chart will disappear if the user clicks somewhere else:
On Error Resume Next 'the next line will error if there's no chart called PopUpChart
ActiveSheet.Shapes("PopUpChart").Delete
On Error GoTo 0 'reset error hadling to normal
If Not Intersect(Target, Columns(2)) Is Nothing Then 'don't do anything if the selected cell isn't in column B
'add chart
Set rangetoplot = Sheets("sheet1").Columns("A").Find(Target.Offset(, -1).Value).Resize(, 12)
'Application.Goto rangetoplot 'debug line
'Me.Activate 'debug line
Set newCht = Me.ChartObjects.Add(Target.Left + Target.Width + 5, Target.Top, 250, 130)
With newCht.Chart
.Parent.Name = "PopUpChart" 'to make it identifiable for deletion
.Type = 4 'a line chart
.SeriesCollection.Add rangetoplot, 1, True
.Legend.Delete 'remove the legend since there's only one line
.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 9 'reduce the size of the chart's title
End With
End If
End Sub

p45cal
07-01-2014, 05:19 PM
Also, i forgot to tell you that this will be uploaded on a shared resource and will be used every week.
See attached.




I was wondering if it would be easier to:
1. have a different sheet with all charts
2. export each chart as .jpg or image
3. link the cell to the according chart image.

Thank again,
Hawk.
Well, it would require you to produce the charts yourself every week - what if you're away, ill, get sick of doing it after a few weeks?
It would increase the file size significantly.
I'm not sure how you would link to specific jpgs.

hawk77
07-01-2014, 07:09 PM
See attached.




Well, it would require you to produce the charts yourself every week - what if you're away, ill, get sick of doing it after a few weeks?
It would increase the file size significantly.
I'm not sure how you would link to specific jpgs.

This is just great, really appreciated!