Excel2000 procedure in Excel2007

[Arvi Laanemets]

Hi

I have a workbook designed in Excel2000, where visibility of some seets is
controlled by Change event of SetUp sheet. In Excel2000 it works OK, but
when I open the file in Excel2007 (it's first time I have used any version
of Excel higher than 2000 so long), then there are no errors returned by
VBA, but whenever the Visible property for seet is recalculated, the
property remains unchanged. And when a cell value on sheet is changed by
code, this value remains unchanged too. The code is here:
--------
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" Then
Sheets("SetUp").Range("MyFormat") = "A5"
End If
Sheets("Shipments").Visible = (Sheets("SetUp").Range("MyMode") =
"MultiLabel")
Sheets("MultiLabelA4").Visible = (Sheets("SetUp").Range("MyMode") =
"MultiLabel" And Sheets("SetUp").Range("MyFormat") = "A4")
Sheets("MultiLabelA5").Visible = (Sheets("SetUp").Range("MyMode") =
"MultiLabel" And Sheets("SetUp").Range("MyFormat") = "A5")
Sheets("SingleLabelA5").Visible = (Sheets("SetUp").Range("MyMode") =
"SingleLabel")
End If

End Sub
---------


P.e. I have SetUp!MyFormat="A4" and SetUp!MyMode="Multilabel". When I change
SetUp!MyMode="SingleLabel", in Excel2000 SetuUp!MyFormat is changed to "A5",
sheet SingleLabelA5 is made visible, and sheet Shipments and MultilabelA4
are made invisible. In Excel 2007 nothing happens. When i debug the code
step-by-step in Excel2007, I see, that p.e. the expression
(Sheets("SetUp").Range("MyMode") = "MultiLabel")
returns False, but Sheets("Shipments").Visible remains hidden, etc.

The macro security for Excel2007 is set to low at moment (it is medium for
Excel2000)


Thanks in advance for any help.

 

[Arvi Laanemets]

Sorry, I misstyped here something.

are made invisible. In Excel 2007 nothing happens. When i debug the code
step-by-step in Excel2007, I see, that p.e. the expression
(Sheets("SetUp").Range("MyMode") = "MultiLabel")
returns False, but Sheets("Shipments").Visible remains hidden, etc.

There must be:
Sheets("Shipments").Visible remains visible ...

* Sheet("Shipments").Visible remains -1 (or xlSheetVisible), but it must
change to 0 (or xlSheetHidden), as expression returns False.



Arvi Laanemets

 

[Jon Peltier]

You might convert the true/false of your calculations to the built-in
constants for visible, hidden, and very hidden. I know VBA is usually pretty
forgiving of this kind of confusion, but I've seen other code misfire in
2007 for similar reasons.

- Jon

 

[Arvi Laanemets]

Hi

I tried, but without any effect.

I included an error trapping routine into procedure, and now I know that
really there are 2 problems.

1) The code row
Sheets("SetUp").Range("MyFormat") = "A5"
or
Sheets("SetUp").Range("C3") = "A5"
or
Sheets("SetUp").Range("C3").Value = "A5"

stops the procedure. All watch expressions are set to "Out of context", and
returned error code is 1004. At first I feared, that maybe sheet protection
may cause this in Excel2007 (although A5 / MyFormat is unprotected cell),
and removed the sheet protection, but it didn't help too.

2) When I disable the row where a cell value is changed, then the rest of
code, which contains rows like
Sheets("SheetName").Visible=LogicalExpression
or
Sheets("SheetName").Visible=Iif(LogicalExpression,xlSheetVisible,xlSheetHidden)

runs smoothly, but nothing happens. Unchanged remain both visibility of
sheet in workbook and Visible property of this sheet in VBA editor. And for
VBA there is nothing wrong!

I did overwrite the file over with backup copy - in case the file was
corrupted somehow on first open, but this had no effect too.
Btw, when I include unprotecting/protecting of sheet into procedure, then
the sheet protection remains unchanged too.


Arvi Laanemets

 

[Jon Peltier]

I can't see why you're having these problems. Did you try recreating the
file from scratch?

- Jon

 

[Arvi Laanemets]

Not yet. I don't have enough time at moment, but probably I have to try
later - create a file in Excel2000, add features one-by-one, and after every
step look what happens, when I open the file in Excel2007.