Charts overlapping datatable on spreadsheet.
I'm now having a problem where the graphs on a spreadsheet are now overlapping the datatable. I'm creating the spreadsheet from within access using vba. I thought the following code was working fine but now I see that it really wasn't. I'm trying to offset the top parameter of each chart so they line up below the used range of the datatable.
Sub LineUpMyCharts(objWS As Object)
' Comments:
' Params : objWS
' Modified:
On Error GoTo PROC_ERR
Dim objXlSheet As Object
Dim objChart As Object
Dim sngMyHeight As Single
Dim sngMywidth As Single
Dim lngChartCount As Long
Dim lngChartIndex As Long
sngMywidth = 750
sngMyHeight = 300
Set objXlSheet = objWS
lngChartCount = objWS.ChartObjects.Count
For lngChartIndex = 1 To lngChartCount
Set objChart = objXlSheet.ChartObjects(lngChartIndex)
With objChart
.Top = objXlSheet.UsedRange.Rows.Count + ((sngMyHeight + 50) * lngChartIndex)
End With
Next
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Number & vbTab & Err.Description, vbCritical, Me.Name & ".LineUpMyCharts"
Resume PROC_EXIT
End Sub
<I'm trying to offset the top parameter of each chart so they line up below the used range of the datatable.>
to position the chart below the datatable, you need to get the address of the cell after the datatable
assuming this code
objXlSheet.UsedRange.Rows.
gives you a value of 10
create a variable, Dim chartRange as range
set chartRange=activesheet.ran
With objChart
.Top = chartRange.top
End With
get the idea?
to position the chart below the datatable, you need to get the address of the cell after the datatable
assuming this code
objXlSheet.UsedRange.Rows.
gives you a value of 10
create a variable, Dim chartRange as range
set chartRange=activesheet.ran
With objChart
.Top = chartRange.top
End With
get the idea?
Hello Rey,
Below is my final code, where I implemented your solution. Now I can line up vertically any number of charts. Thanks again for your help.
Below is my final code, where I implemented your solution. Now I can line up vertically any number of charts. Thanks again for your help.
Sub LineUpMyCharts(objWS As Object)
' Comments:
' Params : objWS
' Modified:
On Error GoTo PROC_ERR
Dim objXlSheet As Object
Dim objChart As Object
Dim sngMyHeight As Single
Dim sngMywidth As Single
Dim lngChartCount As Long
Dim lngChartIndex As Long
Dim chartRange As Object
Dim lngRowCount
sngMywidth = 750
sngMyHeight = 300
Set objXlSheet = objWS
lngRowCount = objXlSheet.UsedRange.Rows.Count + 1
Set chartRange = objXlSheet.range("A" & lngRowCount)
lngChartCount = objWS.ChartObjects.Count
For lngChartIndex = 1 To lngChartCount
Set objChart = objXlSheet.ChartObjects(lngChartIndex)
With objChart
If lngChartIndex = 1 Then
.Top = chartRange.Top + 50
Else
.Top = chartRange.Top + 50 + ((sngMyHeight + 50) * (lngChartIndex - 1))
End If
End With
Next
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Number & vbTab & Err.Description, vbCritical, Me.Name & ".LineUpMyCharts"
Resume PROC_EXIT
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial