EMERGENCY! Deadline this coming Tuesday!! (10/15/02) [Excel

[Thomas]

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

 

[stevebausch]

Have you attempted to contact the author? You may not have the most recent version.

 

[Thomas]

I've attempted to contact the author, but given the weekend, I don't expect an answer back soon (at least not before deadline!). I've brought it to this board, because I know there are some real gurus out there that may be able to help out .

If I receive a response before it is solved here, I'll post the solution .

Thanks again;
- Thomas

 

[stevebausch]

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)

Here's a guess. Immediately after the chart label is created, before you do anything else, right-click on the chart label and select the option that has 'format' in it's name.

What sort of properties are available for this item? Seem to recall a Properties tab that deals with sizing and positioning.

 

[Thomas]

Well, I got an answer from the author; wasn't what I was expecting, but at least gives me a direction to go about trying to get around this:

****************************************
Hi Thomas,

Yes, I know exactly what you're talking about and unfortunately it's a
bug in Excel. If you were using the very limited built-in labeling abilities
that Excel provides, the data labels would follow along properly as you
changed your data series.

With custom labels, unfortunately, it doesn't work this way. For some
reason, as soon as you create a custom label for a data point it becomes
fixed to the data point at that position in the chart, regardless of how the
underlying series changes.

I've been complaining to Microsoft about this since Excel 5, but I guess
it's not very high on their list of priorities. There's really no solution
at this point other than to re-label the chart each time the data series is
modified. Sorry I couldn't give you a better answer.

Rob Bovey, MCSE, MCSD
Application Professionals
http://www.appspro.com/
****************************************

I think I will just add an option button to the charts for "Show Comments" or "Don't Show Comments", ahich will re-run the chart labeller, and thus avoid this problem altogether. I'll update on how things turn out.

For those that replied, thanks a bunch!

Peace;
- Thomas

 

[stevebausch]

Translation of Microsoft's position:

Go buy some charting software.
We have some for sale. Made by graphic artists, for graphic artists.

Excel is for accountants.


Can't say I blame them.