score:1
added a refresh button next to chart which adjust the data labels. below is the code behind the button.
sub movelabels()
dim sh as worksheet
dim ch as chart
dim sers as seriescollection
dim ser as series
dim i as long, pt as long
dim dlabels() as datalabel
set sh = activesheet
set ch = sh.chartobjects("chart 1").chart
set sers = ch.seriescollection
redim dlabels(1 to sers.count)
for pt = 1 to sers(1).points.count
for i = 1 to sers.count
set dlabels(i) = sers(i).points(pt).datalabel
next
resetlabels dlabels
adjustlabels dlabels ' this sub is to deal with the overlaps
next
end sub
private sub adjustlabels(byref v() as datalabel)
application.screenupdating = false
dim i as long, j as long, adj as long
dim temp_a as string, temp_b as string
for i = lbound(v) to ubound(v) - 1
for j = lbound(v) + 1 to ubound(v)
temp_a = v(i).caption
temp_b = v(j).caption
debug.print temp_a & " - | - " & temp_b
v(i).caption = "a"
v(j).caption = iif(temp_a = temp_b, "a", "b")
activesheet.chartobjects("chart 1").activate
if ((v(j).top = v(i).top) and (v(i).caption <> v(j).caption) and (v(j).left = v(i).left)) then
select case v(j).position
case xllabelpositionabove
v(j).position = xllabelpositionright
case xllabelpositionright
v(j).position = xllabelpositionbelow
case xllabelpositionbelow
v(j).position = xllabelpositionleft
case xllabelpositionleft
v(j).position = xllabelpositionabove
end select
end if
v(i).caption = temp_a
v(j).caption = temp_b
temp_a = vbnullstring
temp_b = vbnullstring
next j, i
application.screenupdating = true
end sub
sub resetlabels(byref v() as datalabel)
for i = lbound(v) to ubound(v) - 1
v(i).position = xllabelpositionabove
next
end sub
score:1
you can:
- select a single data label. click on any data label, and it will select the set of data labels. click again on any data label of that set, and it will select that specific label. or click on any object in the chart, and use the left/right arrows to change the selection, until you have selected the label of interest.*
- move it. click and drag.
see https://stackoverflow.com/a/27813339/2707864 (related).
for an automated work, i suggest you get the awesome xy chart labeler and use it as a basis for your vba code. the required code will not be short. i give you here a schematics:
- detect whether there would be an overlap (you have to check not only for exact coincidence-complete overlap-, but within some x-y box-partial overlap-). you might need to detect multiple complete/partial overlaps. under some circumstances (perhaps unlikely for you), this might be quite complex. in an extreme case, all data points may form a chain of partial overlaps.
- decide on an algorithm for moving labels, depending on the detected cases above.
- use the code in xy chart labeler to perform the move.
* it is quite instructive to see how this works, sometimes you would be able to select an object that would be otherwise difficult/impossible to select with the mouse.
Source: stackoverflow.com
Related Query
- excel bubble chart overlapping data label
- Excel macro to fix overlapping data labels in line chart
- Excel XY Chart (Scatter plot) Data Label No Overlap
- How to change data label width in an Excel chart with VBA?
- Extract Data Label from Excel Chart
- Adding Data Label to Excel Chart - Time Consuming
- VBA to change data label in a chart - Compatibility btw Excel 2013 and 2010
- Copy a chart from one sheet to another using the target sheet data for the chart in Excel
- excel vba changing bar chart color for a data point based on point value
- Change color of data series in excel xyscatter chart
- Add multiple data series to excel chart with VBA
- Color data points based on data label text - Excel VBA
- Excel - dynamic chart x axis - ignore x categories with no data
- How can I make sure Excel does not change number format of a data label in other language versions?
- Displaying "live" chart data in Excel Userform
- Keep Chart Data Label from Wraping
- Retrieve excel chart data from powerpoint slide (programmatically)
- Excel VBA remove overlapping of data into adjacent fields
- How to create a chart that has two data sets using Excel VBA 2010
- PowerPoint VBA - Chart Color Based on Data Label in Horizontal Axis
- Using VBA to Paste Excel Chart with Data into PowerPoint
- Adding webpage link to each data point or data label on an Excel scatter plot
- Excel 2003 Charting: Chart Data Too Complex
- Excel to copy realtime data and create a chart out of it
- chart in excel with user data selection
- Excel VBA - Get chart data range
- Excel VBA chart, show data label on last point only
- Elegant way to highlight chart data series in Excel
- Locking data to an Excel Chart
- Can Excel scatter chart background colours be customized based on data values?
More Query from same tag
- How do I ignore the first column in SQL Insert instruction via Excel VBA?
- Pasting the checked items of a Sheet to the colored cells of another sheet in VBA Excel
- Changing how a variable is declared based on the size of the worksheet?
- Copy ONLY text from one range and paste ONLY the first three text on another sheet
- Does the order that column names, from an ADO recordset, are referenced in VB6 or VBA code impact execution speed?
- How can I include a hyperlink to a cell in another sheet?
- Save multiple worksheets as PDF
- To paste the data on the last row of data inputed?
- VBA Factory method with Private Variables
- Highlight found text if it is a complete word, count separation of repeated words, process selected text
- VBA - Delete all rows after last row in copied data
- vba extract array from array of arrays
- VBA Change Cell Value On Insert
- how to traverse column from table header in vba
- VBA to keep format when copying shapes from Powerpoint to Excel
- How do I sort a given range alphebetically and keep the cell names tied to the cells being sorted?
- Excel VBA multiple selection ListBox check if nothing is selected
- How to import SAS Programs into SAS Add-In for Microsoft Office through Excel VBA
- RMTrak Visual Basic Run Time Error 4248
- Having trouble with hiding a template sheet
- Replacing excel data using another spreadsheet
- Finding a value in cell B(x) and replace value in C(x). Offset by 1
- Excel VBA - best practice to call a function upon detecting a change in cell value
- Outlook Auto Response
- Inserting multiple rows in very large worksheet
- VBA: Creating a PivotTable on New Sheet
- Get existing IE via VBA
- Hidden data in excel
- MS Access Looping a union query through all columns?
- How to retain focus on selected record on a subform?