• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Graph data labels need to give text based on the value selected

V

vinwin06

New Member
hi all,


i have a chart in sheet1 and i have series of text and related chart data in sheet2. if any user select any data point in the chart, then based on the value selected the related text need to be displayed from sheet2.


how i can do that in excel graphs.
 
Hi vinwin06,


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


Regarding uploading file,please see the below link:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Now regarding your question, I would request you to please upload a sample workbook (in order to help us better understand your data layout) and clarify the below points:


1)How exactly the text should be displayed(somewhere in the cell or separately inside a textbox)?

2)Do you mean to say that clicking on each data label, the relevant/corresponding texts should be displayed?


Kaushik
 
hi kaushik,


thanks for the response. kindly get the sample file in the link below;


http://rapidshare.com/files/1152172019/Sample%20Graph.xlsx


1. text should be displayed inside text box with close option.

2.yes if the user click particular data label then relevant text need to displayed from column c.
 
Hi vinwin06,


I am really very sorry for replying you so late my friend!!Actually it's been a very hectic schedule for me for last couple of days.


Anyways, let's talk about your work now. I am able to achieve what you are looking for. Please understand I am not able upload the workbook( with solution) now as I am posting this from my office network. So please follow the below instruction to implement the same in your workbook.


1) Recreate the chart in the "Chart sheet". To do the same, please perform the below steps(I assume you are using excel 2007):


i)Select your existing chart (in sheet1)

ii)Right click (on chart area). You will find an option called "Move Chart"(7th option from the top). Click on the option to bring "move chart" dialogue box.

iii)By default, "object in" will be selected. You need to select "New sheet" and hit "OK" button.


This process will give you a new sheet called "Chart1" at the begining.


2)Now, press ALT+F11 from your keyboard to bring the Visual Basic Editor.

3)Double click on Chart1 sheet(you will find that in the left hand side...project explorar window) to bring chart sheet code module(blanc Code Window on the right)

4)Paste the below code in the module:


Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _

ByVal x As Long, ByVal y As Long)


Dim ElementID As Long, Arg1 As Long, Arg2 As Long

Dim myX As Variant, myY As Double


With ActiveChart

' Pass x & y, return ElementID and Args

.GetChartElement x, y, ElementID, Arg1, Arg2


' Did we click over a point or data label?

If ElementID = xlSeries Or ElementID = xlDataLabel Then

If Arg2 > 0 Then


TempStat = Sheet1.Range("C" & (Arg2 + 1)).Value

MsgBox TempStat

End If

End If

End With


End Sub


Now you are good to go. Go back to your chart sheet and click on any data label point. Clicking on any data label point, you should get a message box conataining the status (good/poor etc) of the corresponding marks.


I prefer not to use any textbox as you are looking for close option of that message as well. Using messagebox is more userfriendly and easy to implement as well.


In case you face any problem in following/implementing the instruction to your workbook, I will upload the workbook with solution tomorrow morning. Hope it is fine with you.


Best Regards,

Kaushik
 
Hi Kaushik,


Thanks for your time in detail explanation. i did everything but when i click the data label it showing the error of object required and it stops in this line of code "TempStat = Sheet1.Range("C" & (Arg2 + 1)).Value".


link of the updated file


http://rapidshare.com/files/1272009744/Sample%20Graph.xlsm


your help is appreciated.
 
Hi, vinwin06!


In the Chart1 VBA module code section, you must change this:

TempStat = Sheet1.Range("C" & (Arg2 + 1)).Value

by this:

TempStat = Worksheets("Sheet1").Range("C" & (Arg2 + 1)).Value


Adding an Option Explicit clause at the beginning of the module is recommendable. Then you should declare TempStat variable too.


The code -after laundry process- looks like this:

-----

[pre]
Code: 
Option Explicit

Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double
Dim TempStat As String

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2
' Did we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
TempStat = Worksheets("Sheet1").Range("C" & (Arg2 + 1)).Value
MsgBox TempStat
End If
End If
End With

End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
Oh!!....my apologies...it should be: TempStat = Worksheets("Sheet1").Range("C" & (Arg2 + 1)).Value


Thanks SirJB7 (my favorite "Pablo Sir") for correcting this and write the code in more refined way.


I absolutely agree with SirJB7 regarding adding an Option Explicit clause at the beginning of the module and defining all the variables you are using in the code as a best practice. Thanks for pointing this Pablo Sir.


Regards,

Kaushik
 
SirJB7,


How do you properly maintain the indentation of the code when you post it here?


I write the code maintaining the correct indentation in the module but when I post it here( copy and paste) entire indentation goes for a toss. I try to fix it by pressing "tab" key here but once I post it nothing comes properly.


Can you plz advise me what should I do to keep the original indentation of the code while posting here?


Kaushik
 
Hi Kaushik ,


Use the backtick ( ` ) , which is on the key to the left of the 1 in the top row ( the row above Q W E R T Y U I O P ) , before and after your code ; this will retain the original indentation.


Narayan
 
Hi, vinwin06!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!


@Kaushik03

Hi!

As NARAYANK991 wrote, and as stated right here in this page, below the Tags field under the Reply field.

Regards!
 
Hi SirJB7,


You guys deserved for these words its my pleasure to told this to you.


I have some questions for trying to understand better;


1. Why we need to move the graph into separate sheet and include this code. Its not possible to work these codes in sheet1 itself?

2. Can we implement same codes for bar graph also?


Regards,
 
Hi Guys,


Hope you all busy in weekends i have got the answer for que.2 in my previous post.

now i need to know only question 1 why we need to move the chart into separate sheet can we implement in sheet1 itself.
 
Kaushik03


If you have a look at the bottom of this page and every post page, just under the Tags(comma separated) you'll see an innocuous set of instructions "Allowed markup..."


You can also read about them here: http://chandoo.org/wp/2011/11/04/fancy-posts-using-html-display-codes/
 
My apologies Hui...I actually should have seen these instructions...but somehow I just missed it...probably I may need to check my focal length....:)


And yes, thank you for sharing this link...


Good night to you...


Kaushik
 
Hi SirJB7,


You guys deserved for these words its my pleasure to told this to you.


I have some questions for trying to understand better;


1. Why we need to move the graph into separate sheet and include this code. Its not possible to work these codes in sheet1 itself?
 
Hi All,


Can you please help me on this code only. in the above mentioned the comments are based on the next column but shall i use vlookup function instead of getting at next column.


TempStat = Worksheets("Sheet1").Range("C" & (Arg2 + 1)).Value


This code need to be changed with vlookup code, like the selected value need to be vlookup and search for comments so that i can paste the comments in any columns.
 
Hi vinwin06,


Glad to hear from you again...


I do understand your concern but not sure where you actually want to paste the vlookup-ed data(comments)....I mean to say in which sheet...which column etc...


I assume, you want to paste the data on sheet2 from A2 onwards(A1 is header).


Please replace the earlier code by the following one in the same place...

[pre]
Code: 
Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double
Dim TempStat As String
Dim Tempval As String
Dim lstRow As Long

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Check if we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then

TempStat = Worksheets("Sheet1").Range("C" & (Arg2 + 1)).Value

'Lookup the respective comments

Tempval = Application.WorksheetFunction.VLookup(Sheet1.Range("B" & (Arg2 + 1)).Value, Sheet1.Range("B:C"), 2, False)

'You can change the sheet and column reference to your suit where you want to paste the data
lstRow = ThisWorkbook.Worksheets("sheet2").Range("A65000").End(xlUp).Row + 1
Sheet2.Range("A" & lstRow).Value = Tempval

'                MsgBox TempStat
End If
End If
End With

End Sub
[/pre]

Click on any data label, the respective comments should populate at sheet2 one after another as you keep clicking on data labels.


Hope this is fine...


Regards,

Kaushik
 
Thanks Kaushik its really helpful but now i am again with tricky question for me, instead of vlookup can we use Hlookup or offset formula , because suppose if i have like below;


Students A B C D E F G

marks 35 40 55 50 68 75 85

cooments poor - study well poor need to improve need to improve ok Good Good


its like based on the marks it need to hlookup even i tried but i dont know how to fix lookup value.


Hope you can help me on this.
 
Hi vinwin06,


According to your suggested change in the chart data layout, I have modified the code again in order to meet your requirements.


Below here is the modified version of the code:

[pre]
Code: 
Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double
Dim TempStat As String
Dim Tempval As String
Dim lstRow As Long

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Check if we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then

'According to your new suggested data layout, respective comment will be picked up as you clicked on the data label on the chart
Tempval = Worksheets("Sheet1").Cells(3, (Arg2 + 1)).Value

'You can change the sheet and column reference to your suit where you want to paste the data
lstRow = ThisWorkbook.Worksheets("sheet2").Range("A65000").End(xlUp).Row + 1
Worksheets("Sheet2").Range("A" & lstRow).Value = Tempval
MsgBox "Comment is successfully picked up and placed in sheet2. Please visit sheet2 for the same."
End If
End If
End With

End Sub
[/pre]

Please also see the file here:

http://speedy.sh/8UUX2/Sample-Graph.xlsm


Click on the chart data label and check sheet2 for the comments.


Please check this out and let us know if this is fine..

Kaushik
 
Hi Kaushik,


Thanks for the reply but your code not showing the comment instead of it saving it in sheet2. I think i did not clearly tell my requirement . please have a look at in the sample file i have provided.


In my sample my comments are in row no 14 in this case for picking the comment and show it as msgbox can we use the offset formula for the same.


Tempval = Application.WorksheetFunction.VLookup(Sheet1.Range("B" & (Arg2 + 1)).Value, Sheet1.Range("B:C"), 2, False)


MsgBox Tempval


In this code instead of vlookup we need to use offset to show the comments in graph itself. sorry for confusion.


http://rapidshare.com/files/1260066891/Sample%20Graph_1.xlsm
 
Hi vinwin06,


I provided the code in that way as you wanted to paste the comments in some other column (according to your last post).


However, thank you for providing the clarification.


If your comments are at row 14 (always), then we can directly change the row reference number in the below line of the code and show the value in a messagebox in the chart itself as you click on the data label:


Tempval = Worksheets("Sheet1").Cells(14, (Arg2 + 1)).Value

MsgBox Tempval


Anyways, below here is the complete code:

[pre]
Code: 
Private Sub Chart_Mousedown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double
Dim TempStat As String
Dim Tempval As String
Dim lstRow As Long

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Check if we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then

'According to your new suggested data layout, respective comment will be picked up as you clicked on the data label on the chart
Tempval = Worksheets("Sheet1").Cells(14, (Arg2 + 1)).Value
MsgBox Tempval
End If
End If
End With

End Sub
[/pre]

Below here is the link for the file:

http://speedy.sh/7z3DP/Sample-Graph-1-Updated.xlsm


Let me know if it meets your requirement.


Regards,

Kaushik
 
hi Kaushik,


thanks once again for your time. that's all i wanted . i appreciate all your efforts.


My final question why we need to move the graph into another sheet why can't we keep it in sheet 1 itself and try this code. any specific reason behind it...?
 
You must log in or register to reply here.
Back
Top