Solved

VBA Color point in Chart Excel

Posted on 2016-09-02
8
21 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
  • 3
  • 3
8 Comments
 
LVL 48

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 48

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 48

Expert Comment

by:Rgonzo1971
ID: 41781165
0
 

Author Comment

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

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now