I really thought I had this figured out, but I've caught one last snag on it: I've created a series of dynamic trend charts, complete with zooming and scrolling capabilities (much thanks to some tricky named ranges and offset functions!), but here's the last thing that's killing me:
I've picked up a wonderful chart labeling tool via shareware called 'XY Chart Labeler'. Wonderful tool certainly, but I'm having a problem giving it a named range for the chart comments. The comment will appear in the appropriate place at first, so I know the named range is working, but when I start zooming and scrolling the chart(s), the chart label remains in the same place it started on the graph! (i.e., does not move with the named range and the rest of the data series)
Below, I've posted the code of the XY Chart Labeller that I believe deals with this function of the tool... please help me figure out how to make this accept dynamic named ranges that update (scroll/zoom) with the chart(s)!
Thanks in advance;
- Thomas
code:
***************************************
Public Function rngGetLabelRange(ByVal szRangeString As String) As Excel.Range
Dim rngLabelRange As Excel.Range
Dim wksLabelSheet As Excel.Worksheet
Dim szSheet As String
Dim szRange As String
Dim szEvalName As String
Dim szLabelError As String
If gbDEBUG_MODE Then
On Error GoTo 0
Else
On Error GoTo ErrorHandler
End If
Set rngLabelRange = Nothing
''' Labels were selected in a different workbook.
If InStr(szRangeString, "[") <> 0 Then
Err.Raise Number:=glHANDLED_ERROR, Description:=gszERR_OTHER_WORKBOOK
''' Labels are on a different sheet than the chart.
ElseIf InStr(szRangeString, gszBANG) <> 0 Then
Set rngLabelRange = Application.Range(szRangeString)
''' Labels are on the same sheet as the chart.
ElseIf InStr(szRangeString, "$") <> 0 Then
If TypeOf ActiveSheet Is Worksheet Then
szSheet = ActiveSheet.Name
szRange = szRangeString
Set rngLabelRange = ActiveWorkbook.Worksheets(szSheet).Range(szRange)
End If
''' Might be an unqualified range name.
Else
On Error Resume Next
Set rngLabelRange = Nothing
Set rngLabelRange = Application.Range(szRangeString)
If gbDEBUG_MODE Then
On Error GoTo 0
Else
On Error GoTo ErrorHandler
End If
End If
If Not rngLabelRange Is Nothing Then
Set rngGetLabelRange = rngLabelRange
Else
Err.Raise Number:=glHANDLED_ERROR, Description:=gszERR_GENERIC_LABEL_RANGE
End If
Exit Function
ErrorHandler:
If Len(gszErrMsg) = 0 Then gszErrMsg = Err.Description
If Err.Number <> glHANDLED_ERROR Then gszErrMsg = gszErrMsg & " (rngGetLabelRange)"
Set rngGetLabelRange = Nothing
End Function
I've picked up a wonderful chart labeling tool via shareware called 'XY Chart Labeler'. Wonderful tool certainly, but I'm having a problem giving it a named range for the chart comments. The comment will appear in the appropriate place at first, so I know the named range is working, but when I start zooming and scrolling the chart(s), the chart label remains in the same place it started on the graph! (i.e., does not move with the named range and the rest of the data series)
Below, I've posted the code of the XY Chart Labeller that I believe deals with this function of the tool... please help me figure out how to make this accept dynamic named ranges that update (scroll/zoom) with the chart(s)!
Thanks in advance;
- Thomas
code:
***************************************
Public Function rngGetLabelRange(ByVal szRangeString As String) As Excel.Range
Dim rngLabelRange As Excel.Range
Dim wksLabelSheet As Excel.Worksheet
Dim szSheet As String
Dim szRange As String
Dim szEvalName As String
Dim szLabelError As String
If gbDEBUG_MODE Then
On Error GoTo 0
Else
On Error GoTo ErrorHandler
End If
Set rngLabelRange = Nothing
''' Labels were selected in a different workbook.
If InStr(szRangeString, "[") <> 0 Then
Err.Raise Number:=glHANDLED_ERROR, Description:=gszERR_OTHER_WORKBOOK
''' Labels are on a different sheet than the chart.
ElseIf InStr(szRangeString, gszBANG) <> 0 Then
Set rngLabelRange = Application.Range(szRangeString)
''' Labels are on the same sheet as the chart.
ElseIf InStr(szRangeString, "$") <> 0 Then
If TypeOf ActiveSheet Is Worksheet Then
szSheet = ActiveSheet.Name
szRange = szRangeString
Set rngLabelRange = ActiveWorkbook.Worksheets(szSheet).Range(szRange)
End If
''' Might be an unqualified range name.
Else
On Error Resume Next
Set rngLabelRange = Nothing
Set rngLabelRange = Application.Range(szRangeString)
If gbDEBUG_MODE Then
On Error GoTo 0
Else
On Error GoTo ErrorHandler
End If
End If
If Not rngLabelRange Is Nothing Then
Set rngGetLabelRange = rngLabelRange
Else
Err.Raise Number:=glHANDLED_ERROR, Description:=gszERR_GENERIC_LABEL_RANGE
End If
Exit Function
ErrorHandler:
If Len(gszErrMsg) = 0 Then gszErrMsg = Err.Description
If Err.Number <> glHANDLED_ERROR Then gszErrMsg = gszErrMsg & " (rngGetLabelRange)"
Set rngGetLabelRange = Nothing
End Function