 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008Excel2000 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 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.  SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) 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 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 _______ > Sorry, I misstyped here something. > [quoted text clipped - 10 lines] > > 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 > 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 [quoted text clipped - 22 lines] >> >> Arvi Laanemets 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 _______ > Hi > [quoted text clipped - 60 lines] >>> >>> 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. SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) >I can't see why you're having these problems. Did you try recreating the >file from scratch? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.