Solved

VBA Color point in Chart Excel

Posted on 2016-09-02
8
25 Views
Last Modified: 2016-10-03
Dear All,

could your please help me with the Excel Color Chart?

I am really Need in your help!

I have a Chart in Excel,
The Chart Shows the Name of the product and two values.I created it withour Pivot Table.

Could you please help me,how can i do it in VBA,I want to choose the one product,double clicked on the product,and the point only of the choosing product will be automatically change the Color in the graph?

is it possible also to add the Name of the choosing product on the upp of the dot ?


I am very appreciate your help!Please help me...
Excel-VBA.xlsx
0
Comment
Question by:Yana Burmistrova
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41781149
Hi,

pls try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set myRng = ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1)

Set SerCol = ActiveSheet.Shapes(1).Chart.SeriesCollection(1)
ActiveSheet.Shapes(1).Chart.ClearToMatchStyle
On Error Resume Next
SerCol.DataLabels.Delete
On Error GoTo 0
If Not Intersect(Target, myRng) Is Nothing Then
    res = 0
    On Error Resume Next ' find corresponding XValue
    res = WorksheetFunction.Match(UsedRange.Cells(Target.Row, 2), SerCol.XValues, 0)
    On Error GoTo 0
    If res <> 0 Then ' if found
        SerCol.Points(res).MarkerBackgroundColor = RGB(255, 0, 0)
        SerCol.Points(res).ApplyDataLabels
        SerCol.Points(res).DataLabel.ShowCategoryName = True
        SerCol.Points(res).DataLabel.ShowValue = False
    End If
End If
End Sub

Open in new window

Regards
Excel-VBAv2.xlsm
0
 

Author Comment

by:Yana Burmistrova
ID: 41781154
everything is great!!!!

Thank you so much!

But is it possible to do ,Show upp of the dot the Name of the product,not the value?
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41781156
Corrected code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set myRng = ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Offset(1)

Set SerCol = ActiveSheet.Shapes(1).Chart.SeriesCollection(1)
ActiveSheet.Shapes(1).Chart.ClearToMatchStyle
On Error Resume Next
SerCol.DataLabels.Delete
On Error GoTo 0
If Not Intersect(Target, myRng) Is Nothing Then
    res = 0
    On Error Resume Next ' find corresponding XValue
    res = WorksheetFunction.Match(UsedRange.Cells(Target.Row, 2), SerCol.XValues, 0)
    On Error GoTo 0
    If res <> 0 Then ' if found
        SerCol.Points(res).MarkerBackgroundColor = RGB(255, 0, 0)
        SerCol.Points(res).HasDataLabel = True
        SerCol.Points(res).DataLabel.Text = UsedRange.Cells(Target.Row, 1)
    End If
End If
End Sub

Open in new window

0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Yana Burmistrova
ID: 41781161
For example, when we choose the product,in the graph the Color Change and Shows the value,is it possible to Show the Name of the product,not the value?
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41781165
0
 

Author Comment

by:Yana Burmistrova
ID: 41781170
GREAT!!!!!!!!!

THANK YOU SO MUCH!!!!!!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question