Cell protection / Compatibility XL97/XLXP

  • Thread starter Thread starter Steve Jones
  • Start date Start date
S

Steve Jones

Hi

I have been having problems opening a file in Excel XP that has been running
fine in Excel 97.

More by luck than judgement I have discovered that it is related to cell
protection. The worksheet is protected in XL 97 so that the user can only in
certain cells. When I try and open the file in Excel XP it will not even run
the Workbook_Open macro.

If I unprotect the worksheet in XP and then open the file, the file opens as
it should. I have looked at TOOLS,PROTECTION and checked the cells but all
looks OK.

Has anyone come across a similar problem? what was/is the solution?

Thanks Steve
 
Do you really mean that the workbook_open won't run or that it won't run
successfully?

If it won't run at all, make sure you have Tools|Macro|Security set to something
that allows macros to run (medium or low). The number of options was changed in
xl2k.

If you mean that it will run, but it blows up, then post the snippet of code
that causes problems.

One thing that xl2002 got more stringent with is protection of worksheets. You
used to be able to add:

worksheets("sheet1").protect userinterfaceonly:=true

in xl2k and xl97. In xl2002, if that worksheet is protected with a password,
you have to provide the password to add the userinterfaceonly stuff.

(If that's not it (and I bet it's not), you should post your code.)
 
Hi Dave

Thanks yet again for your time.

Option Explicit
Private Sub Workbook_Open()


Range("A21").Select
Selection.Font.ColorIndex = 2
Application.OnTime Now() + TimeValue("00:00:05"), "Types"
' After 10 secs Run Types macro displays userform to prompt user for
action
'
Application.ScreenUpdating = False
Range("L11").Value = ""
Application.DisplayFormulaBar = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Order Form").Visible = True
' Application.CommandBars("Worksheet Menu Bar").Enable = False
Application.DisplayStatusBar = False
ActiveSheet.Buttons("53").Visible = True 'print
ActiveSheet.Buttons("54").Visible = True ' rept cust
ActiveSheet.Shapes("WordArt 65").Visible = False ' Copy
Range("AD23").Value = "FALSE"
Range("AE23").Value = "FALSE"
Range("AF23").Value = "FALSE"
Range("AG23").Value = "FALSE"
Range("AH23").Value = "FALSE"
Range("AI23").Value = "FALSE"
Range("AJ23").Value = "TRUE"
' Range("AB59").Value = "FALSE"
Range("j2").Select
Worksheets("ORDER").ScrollArea = "a1:o58"
' Resets all FP & BC options to FALSE


End Sub


The code crashes on line two - "selection.font.colourIndex"
Run-time error 1004
Unable to set colourIndex property etc.

I have remmed this line out , run the program again and it has stopped the
program, gone into some type of recovery file mode saying did I want to send
error report to Microsoft, closed the file etc.

I'm not experienced in programming Excel so I'm guessing that it can't be
that complicated or alternatively I have "confused Excel" somewhere.

As said previously the program runs fine in Excel 97.

It will also run in Excel XP without TOOLS,PROTECTION on. (Macro security is
Low as suggested).

What I will do to save taking up any more of your time is to not protect the
file and then protect the file after workbook_open has run and before the
user starts using it.

I'm totally confused and frustrated by this - and not exactly sure what to
try next!

Thanks for all your efforts / suggestions

Steve
 
I don't see anything that would cause excel to crash. But I think I'd add a
line at the top to make sure I was on the correct worksheet.

worksheets("sheet1").select

So that all your selections/activesheets point to the correct sheet.

Not a help, but when you protect your worksheet, you could add
userinterfaceonly:=true and then your macro will be able to do somethings that
the user can't.

But the crashing sounds bad. When I get these things, I get nervous. You could
try Rob Bovey's code cleaner at http://www.appspro.com.

I've seen lots of posts where this can clean up a lot of unexplainable problems
(insert twilight zone theme here).

The other thing that you may want to try is to recreate the workbook (or at
least small portions of it. Enough to get the structure correct.)

Then copy your cleaned modules from the bad version to the test version to see
if that works. (I've only seen a couple of corrupt workbooks (and xl2002
actually handled them better), but maybe your file is going bad. (It's a lot of
work to copy formulas/data/headers/footers/range names/validity/all that
stuff--so recreating them is always a last resort.)

good luck,
 
Back
Top