Excel 2003 - VBA - ActiveCell issue

[Craig Brandt]

I'll try to get this as concise as possible. I have a macro that creates a
chart and places it on the activesheet ("Param"). I save the name of that
chart and store it on the sheet ("Param"), so that I know the name and can
delete the chart later when I create another.

I recently added a feature to the workbook and have a macro on the "Param"
sheet that reacts to change. I use this "change" macro to monitor for a
change in a cell that assigns the number of days to display and this works
fine under normal operating conditions.

When I execute the macro that makes a new chart, the macro creates the chart
and saves the name of the chart with this line:

Sheets("Param").Cells(10, 8) = ActiveChart.Name

This triggers the "change" routine and I get the following error

Run time error '91':
Object variable or with variable not set.

on the indicated line of code shown below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ActRowX As Integer
Dim ActColX As Integer
ActRowX = ActiveCell.Row '<<<<<< Error occurs here
ActColX = ActiveCell.Column
If ActRowX = 8 And ActColX = 8 Then

 

[Per Jessen]

Hi Craig

When you use a change event, you should use Target, which refer to the cell
just having been changed, as it won't always be same cell as active cell,
when cells are changed by macro.

Try if this solves your problem:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ActRowX As Integer
Dim ActColX As Integer
ActRowX = Target.Row
ActColX = Target.Column
If ActRowX = 8 And ActColX = 8 Then

Regards,
Per

 

[Shane Devenshire]

Hi,

Although it may appear obtuse this is the standard way to handle this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range
Set Isect = Application.Intersect(Target, [H8])
If Not Isect Is Nothing Then
'your code here
End If
End Sub

The above is far more flexible in the long run, for example when a change
can be made to any one of a range of cells, say [H1:J10]