VBA: Labeling a scatter plot with Slicers - implementation

yk2vba

New Member
Joined
Nov 16, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Please help me to fix the error in my code! I'm trying to create a macro to label the points on my scatter plot with a slicer. The chart is an object on the worksheet with its source table. My data is Company (supposed to be a label), product (used as a slicer for the chart, allows only one product per chart), X and Y. I searched for the similar questions and found the solution for the case when the labels are in the known range. But in my case I have tons of Products and each product has its own number of companies, so it's a dynamic list of companies. What i'm trying to do in my solution is:
  1. Identify the range of rows that correspond to a selected slicer (Product) - last row and total number of rows.
  2. After calling Ch.GetChartElement x, y, idNum, a, b - I assume that b is a cell corresponding to XY point within the selected Product. So my thinking is: to find the first row of the range (last row-total rows) and after adding b to it, it will get the correct row number in column A (label).
This is my data and chart:
data_and_chart.JPG

I was able to get the macro code to work partially, but it crashes after clicking on more than one point on the chart.
Here is my code in Class Module:

VBA Code:
' ** Class module named Class1 **

Public WithEvents Ch As Chart

Dim idNum As Long
Dim a As Long
Dim b As Long

Private Sub Ch_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim Txt As String
Txt = ""
Dim TotalRows As Long
Dim LastRow As Long
Dim r As Range

LastRow = Sheets("table_chart").Cells.SpecialCells(xlCellTypeLastCell).Row
TotalRows = Sheets("table_chart").Range("A1:A" &LastRow).SpecialCells(xlCellTypeVisible).Rows.Count
MsgBox TotalRows

Ch.GetChartElement x, y, idNum, a, b

If idNum = xlSeries Then
With ActiveChart.SeriesCollection(a).Points(b)
.HasDataLabel = True

'The goal of Txt string - for each XY on a chart find a Company Name in Column A.
' Sheets("table_chart") points to a tab with a table
' Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible) is to find the Column A Range based on Product selection in Slicer
' Cells(LastRow - TotalRows - 1 + b, 1) to find the Company that corresponds to clicked XY point on the chart

Txt = Sheets("table_chart").Range("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cells(LastRow - TotalRows - 1 + b, 1).Value

With .DataLabel
.Text = Txt
.Position = xlLabelPositionAbove
.Font.Size = 8
.Border.Weight = xlHairline
.Border.LineStyle = xlAutomatic
.Interior.ColorIndex = 19
End With
End With
End If
End Sub

Private Sub Ch_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
' clear old marker
On Error Resume Next
Ch.SeriesCollection(a).Points(b).HasDataLabel = False
End Sub

Private Sub Ch_BeforeRightClick(Cancel As Boolean)
Cancel = True
End Sub
Private Sub Ch_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Cancel = True
End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,128,207
Messages
5,629,294
Members
416,384
Latest member
frsamiee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top