PDA

View Full Version : Procedure Runs or Fails Depending on How Started



MWE
10-22-2005, 02:09 PM
I have a very simple procedure that has no passed arguements and does some tweaking of the ActiveChart.

If the procedure is executed via Tools | Macro | Macros ... it works fine. However, if it is started via the TextBox which has this procedure as its assigned macro, the macro fails (error = 1004; Select Method of Point Class Failed). I also tried a command button (Take Focus On Click = False) and had the on click procedure call the target procedure -- same results as with the TextBox.

Any idea what is wrong?

Norie
10-22-2005, 02:35 PM
Can you post the code?

Are you selecting anything in the code? Or trying to use the current selection?

MWE
10-22-2005, 06:20 PM
Can you post the code?

Are you selecting anything in the code? Or trying to use the current selection?
I am trying to use the current selection (ActiveChart). I have attached a copy of the test spreadsheet. It is supposed to look at each "point" in the chart and set the color according to the point value: red if < 0 and green if >= zero. At this point I am not sure what it will do:


Initially (yesterday), it seemed to run OK regardless of how it was initiated
Earlier today (when I first posted this thread), it ran OK if started from Tools|Macros|Macros but generated an error if run from a TextBox or CommandButton.
Now it still runs OK if run from Tools | Macro | Macros, but generates an incorrect result (changes the chart background color!) when run from a TextBox or CommandButton

geekgirlau
10-22-2005, 07:43 PM
As Norie, suggested, you need to make sure the chart is selected. If you use either the text box or the command button, then that object becomes the selection rather than the chart.

Sub xlChartCondFormat0()
Dim I As Long
Dim NegColor As Long
Dim PosColor As Long
Dim SeriesNum As Long
Dim ThisSer As Series
Dim Vals() As Variant


NegColor = 3
PosColor = 50
SeriesNum = 1
On Error Resume Next
ActiveSheet.ChartObjects("othername").Activate
Set ThisSer = ActiveChart.SeriesCollection(SeriesNum)
If Err <> 0 Then
MsgBox "no active chart", vbCritical
Exit Sub
End If
ReDim Vals(ThisSer.Points.Count)
Vals = ThisSer.Values
For I = 1 To UBound(Vals)
ThisSer.Points(I).Select
If Vals(I) < 0 Then
Selection.Border.ColorIndex = NegColor
Selection.Interior.ColorIndex = NegColor
Else
Selection.Border.ColorIndex = PosColor
Selection.Interior.ColorIndex = PosColor
End If
Next I
End Sub

MWE
10-22-2005, 09:02 PM
As Norie, suggested, you need to make sure the chart is selected. If you use either the text box or the command button, then that object becomes the selection rather than the chart.

Sub xlChartCondFormat0()
Dim I As Long
Dim NegColor As Long
Dim PosColor As Long
Dim SeriesNum As Long
Dim ThisSer As Series
Dim Vals() As Variant


NegColor = 3
PosColor = 50
SeriesNum = 1
On Error Resume Next
ActiveSheet.ChartObjects("othername").Activate
Set ThisSer = ActiveChart.SeriesCollection(SeriesNum)
If Err <> 0 Then
MsgBox "no active chart", vbCritical
Exit Sub
End If
ReDim Vals(ThisSer.Points.Count)
Vals = ThisSer.Values
For I = 1 To UBound(Vals)
ThisSer.Points(I).Select
If Vals(I) < 0 Then
Selection.Border.ColorIndex = NegColor
Selection.Interior.ColorIndex = NegColor
Else
Selection.Border.ColorIndex = PosColor
Selection.Interior.ColorIndex = PosColor
End If
Next I
End Sub
The chart IS selected if you select it manually first before you click on the textbox or command button. I have dozens of procedures that use the "current selection" for this kind of thing and they work fine. For this particular case, if I select the chart and click on the textbox or command button, ActiveChart is correct. The proc pukes and states "no active chart" if no chart has been selected.

BlueCactus
10-22-2005, 10:53 PM
It doesn't like ThisSer.Points(I).Select when run off the sheet. Don't know why. If you change the loop to For I = 1 to 1 to see what is first selected, you get Points(1) when running from the menu, and the ChartObject when running from the sheet. Bizarre. If you omit the offending line and change Selection to ThisSer.Points(I) it works fine.

Norie
10-23-2005, 07:03 AM
The chart IS selected if you select it manually first before you click on the textbox or command button. I have dozens of procedures that use the "current selection" for this kind of thing and they work fine. For this particular case, if I select the chart and click on the textbox or command button, ActiveChart is correct. The proc pukes and states "no active chart" if no chart has been selected.
If you click a textbox/command button it becomes the selected object.

You really should avoid using selection/activation in code, you don't need it and it can obviously cause problems. :)

When I get a chance I'll download your workbook.

Sub xlChartCondFormat0()
Dim I As Long
Dim NegColor As Long
Dim PosColor As Long
Dim SeriesNum As Long
Dim ThisSer As Series
Dim Vals() As Variant

NegColor = 3
PosColor = 4
SeriesNum = 1

Set ThisSer = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(SeriesNum)

ReDim Vals(ThisSer.Points.Count)
Vals = ThisSer.Values

For I = 1 To UBound(Vals)
If Vals(I) < 0 Then
ThisSer.Points(I).Border.ColorIndex = NegColor
ThisSer.Points(I).Interior.ColorIndex = NegColor
Else
ThisSer.Points(I).Border.ColorIndex = PosColor
ThisSer.Points(I).Interior.ColorIndex = PosColor
End If
Next I

End Sub

BlueCactus
10-23-2005, 08:16 AM
There is some inconsistency here because it does correctly set ThisSer and Vals().

MWE
10-23-2005, 04:22 PM
There is some inconsistency here because it does correctly set ThisSer and Vals().
Thanks for you investigation. As mentioned previously, I have used this technique many times when I need to operate on the ActiveChart. I have found many strange problems with Excel charts and the need to perform redundant operations to get things to work, e.g., having to explicitly select or activate a sheet, then a chart, then a series, etc.

I made a small change to the original procedure eliminating the
ThisSer.Points(I).Selection
and then explicitily using ThisSer.Points(I) instead of Selection in the subsequent lines. Now the procedure works fine. The scarey part is that this now working version is where I started a few days ago and was getting an error the first time the code encountered a ThisSer.Points(I).property. Based on my experience with not explicity selecting chart objects before I operate on them, I modified the code to select the point and then use Selection. That worked for a short time (see my 2nd post to this thread) and then stopped working.

Bizarre is as good an explanation as any ...

Norie
10-23-2005, 04:39 PM
MWE

Did you try the code I posted?

It avoids selection and seemed to work with your attachment.

MWE
10-23-2005, 08:04 PM
MWE

Did you try the code I posted?

It avoids selection and seemed to work with your attachment.
Thanks for you interest and reply, but you seem to have missed the major point. The thread issue was the execution method (TextBox vs CommandButton vs Tools|Macro...) coupled with the manual selection of a chart. Avoiding selection (your code) assumes that the target chart is known by some other process (which is not the case). The basic code was pulled from another procedure, so I knew that it works (most of the time -- see previous replies).

Norie
10-24-2005, 02:20 AM
Thanks for you interest and reply, but you seem to have missed the major point. The thread issue was the execution method (TextBox vs CommandButton vs Tools|Macro...) coupled with the manual selection of a chart. Avoiding selection (your code) assumes that the target chart is known by some other process (which is not the case). The basic code was pulled from another procedure, so I knew that it works (most of the time -- see previous replies).
I think you'll find that the reason the code is failing could be because when you are using a textbox/commandbutton to execute it that object is the current selection.

So if you try and refer to the current selection in the code you are referring to the textbox/commandbutton not the chart.

MWE
10-25-2005, 07:46 AM
I think you'll find that the reason the code is failing could be because when you are using a textbox/commandbutton to execute it that object is the current selection.

So if you try and refer to the current selection in the code you are referring to the textbox/commandbutton not the chart.
I have done some testing and what you claim does not appear to be true. If it were, displaying the shape name(s) for the current selection would show only the textbox linked to the macro. But that does not happen. Rather the name for any previously selected textbox (or charts or other shapes) is displayed, but the linked textbox is not.

See attached example. First click on the Shape List text box. The shape listing macro will indicate no shapes selected. Then select any shape (or combination of shapes using shift + click) and then click on the Shape List text box. The name of each shape previously selected will be displayed.