Excel2000 worksheet Change event doesn't work in Excel2003 or higher

[Arvi Laanemets]

Hi


I did have almost same question here a couple of weeks ago, but I didn't get
any help then. Now I have tried to find some solution myself, but the
problem is getting just more confusing - so I decided to put it here again.

I have a workbook designed in Excel2000. All sheets in workbook are
protected, with cells, where changes by user or VBA are excepted, unlocked.
There are 2 sheets (SetUp, Parts) - which are always visible, and 4 sheets
(Shipments, SingleLabelA5, MultiLabelA4, MultilLabelA5) which are visible
depending on values in 2 cells on SetUp sheet. Those 2 cells are defined as
names MyMode=SetUp!$C$2 and MyFormat=SetUp!$C$3. The procedure which is
controlling, which sheets are visible and which are hidden, is the Change
event for sheet SetUp. The same event may overwrite the value of MyFormat.
And it is what is causing problems too.
The code for event is here:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 And (Target.Row = 2 Or Target.Row = 3) Then
If Sheets("SetUp").Range("MyMode") = "SingleLabel" And
Sheets("SetUp").Range("MyFormat") = "A4" Then
Sheets("SetUp").Range("MyFormat") = "A5"
End If
Sheets("Shipments").Visible = IIf((Sheets("SetUp").Range("MyMode") =
"MultiLabel"), True, False)
Sheets("MultiLabelA4").Visible =
IIf((Sheets("SetUp").Range("MyMode") = "MultiLabel" And
Sheets("SetUp").Range("MyFormat") = "A4"), True, False)
Sheets("MultiLabelA5").Visible =
IIf((Sheets("SetUp").Range("MyMode") = "MultiLabel" And
Sheets("SetUp").Range("MyFormat") = "A5"), True, False)
Sheets("SingleLabelA5").Visible =
IIf((Sheets("SetUp").Range("MyMode") = "SingleLabel"), True, False)
End If

End Sub

The problems are:
1. Whenever the value of range MyFormat is changed in sub, the sub crashes.
I added error trapping, and get that:
Err.number = 1004
Err.Source = ""
Err.Description = "Application-defined or object-defined error".
2. Whenever the Visible property for any of worksheets is changed in sub,
nothing happens. The Visible property remains unchanged.

At first I assumed, that some other procedure may interfere with this Change
event.
There is an workbook's Open event, where Autofilter is enabled for sheets
Shipments and Parts. I did drop it, but without any effect.
There is also an UDF. When the value of MyFormat is changed by VBA, this
triggers a recalculation of all formulas on worksheets (although the ranges
MyMode and MyFormat aren't used in any formulas), and the UDF is called from
them. I did switch of this UDF off too (added Exit Function as 1st
executable row into it). Again no effect.

I added a dummy function TestIt, and tried to change the value of MyFormat
or visibility of sheets from there. It worked like treat!

I restored workbooks Open event and the UDF, and designed TestIt as:

Public Sub TestIt()
Sheets("SetUp").Range("MyFormat") =Sheets("SetUp").Range("MyFormat")
End Sub

Now, whenever I change values of MyMode or MyFormat on sheet SetUp, the
Change event is triggered and an error is returned or nothing happens. But
when I then run the procedure TestIt, overwriting MyFormat with its own
value triggers Change event, and this event does run problemlos now - right
sheets are made visible and the value for MyFormat is changed when needed -
but only when TestIt is running.

Currently I'm testing my workbook in Excel2003. When I did post about this
problem 1st time, I did test it in Excel2007, and problems were same.

Has someone any clue, what is going on?
Thanks in advance!

 

[Bob Phillips]

Arvi,

Tough to get a hold on this.

Have you tried turning off events at the start of this procedure, and on
again at the end?

Which sheet is this on?

Can you post the workboo somewhere?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arvi Laanemets]

Hi

I just started with an empty workbook designed in Excel2000 step-by-step,
saving after every step and checking in Excel2003. So long I managed 2
steps:
1. I created a workbook with same sheets, but unprotected, created drop-down
list on sheet SetUp, and created a Change event for sheet SetUp like in
original file. The Change event worked in Excel2003 too.
2. I did protect all sheets except SetUp. No problems again.

So I will continue with this. Or i will stumble upon the cause of problem at
some point, or this was some occasional fluke and the new workbook will be
OK. Whatever the result, I'll report here.

PS. The change event in question is for sheet SetUp. And I can't post the
workbook unedited (it contains some corporate and customer information), and
beter I'll use the time I'd spend on editing it on designing of new workbook
as described above.

 

[Arvi Laanemets]

Hi

It looks like there was some random fluke. I recreated step-by-step a
dublicate of my workbook, and it worked in Excel2003 without any errors now.

Severeal days are wasted searching for a non-existing error :-((