Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 procedure in Excel2007
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 he -------- 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 ( My real mail address: arvi.laanemets<attarkon.ee ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 procedure in Excel2007
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 procedure in Excel2007
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Arvi Laanemets" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 procedure in Excel2007
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" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Arvi Laanemets" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 procedure in Excel2007
I can't see why you're having these problems. Did you try recreating the
file from scratch? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Arvi Laanemets" wrote in message ... 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" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Arvi Laanemets" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 procedure in Excel2007
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. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Jon Peltier" wrote in message ... I can't see why you're having these problems. Did you try recreating the file from scratch? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I had menus of Macros in Excel2000 ... where in Excel2007?? | Excel Discussion (Misc queries) | |||
Excel2007 | Excel Discussion (Misc queries) | |||
How to jump from a Form procedure to a Workbook or Module procedure? | Excel Programming | |||
Excel2000 VBA: How force the procedure to wait until queries are refreshed? | Excel Programming |