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 he

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 a
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!

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )