Protection and link problems

[s4cinfo]

I'm not sure if I have three related problems which are symptoms of
one problem, or three separate problems (see below). The first is the
most troublesome.

1. I have created an Excel workbook that involves complex macros and
multiple worksheets. The worksheets are protected. Whenever I try to
unprotect a worksheet - either directly or via a macro I have written
for the purpose - the worksheet freezes and I get a spinning beach
ball. I have to force quit. Curiously, this only happens with Office
2004 for the Mac and not with Office X for the Mac.

2. On a slightly more expanded version of the worksheet used for
slightly different purposes, there is another problem that also
occurs. On launching, it shows there is a link. I cannot find the link
reference anywhere in the workbook (Excel's diagnostics for finding
links are abysmal). I have tried eliminating worksheets one by one
until there is only one left, but the phantom link keeps appearing. If
I try to break the link, Excel crashes. This happens on both Office
2004 and Office X.

3. In both cases, if I try to disable macros on launching, I get a
message stating that not all macros can be disabled. Help suggests the
presence of old XLM macros, but I do not have a clue where to go with
this.

Any insight greatly appreciated!

Stuart

 

[JE McGimpsey]

I'm not sure if I have three related problems which are symptoms of
one problem, or three separate problems (see below). The first is the
most troublesome.

1. I have created an Excel workbook that involves complex macros and
multiple worksheets. The worksheets are protected. Whenever I try to
unprotect a worksheet - either directly or via a macro I have written
for the purpose - the worksheet freezes and I get a spinning beach
ball. I have to force quit. Curiously, this only happens with Office
2004 for the Mac and not with Office X for the Mac.

Sounds like a corrupt workbook - do you have event code running in
either the worksheets or the workbook? Does it make a difference if you
start XL with the shift key down and disable macros when you open the
workbook (after running the suggestion for (2), below)?

2. On a slightly more expanded version of the worksheet used for
slightly different purposes, there is another problem that also
occurs. On launching, it shows there is a link. I cannot find the link
reference anywhere in the workbook (Excel's diagnostics for finding
links are abysmal). I have tried eliminating worksheets one by one
until there is only one left, but the phantom link keeps appearing. If
I try to break the link, Excel crashes. This happens on both Office
2004 and Office X.

Yup, this is a huge problem with every version of XL (both Win and Mac).
The best utility I've used is FindLink.xla by MVP Bill Manville, which
you can get here:

http://www.bmsltd.co.uk/MVP/Default.htm

3. In both cases, if I try to disable macros on launching, I get a
message stating that not all macros can be disabled. Help suggests the
presence of old XLM macros, but I do not have a clue where to go with
this.

Often this is a problem with either corrupt files or with defined names.
Try FindLink, then see what the behavior is...

 

[Bob Greenblatt]

PMFJI, but I may be able to shed additional light on the problem which may
or may not help. See below

Sounds like a corrupt workbook - do you have event code running in
either the worksheets or the workbook? Does it make a difference if you
start XL with the shift key down and disable macros when you open the
workbook (after running the suggestion for (2), below)?


Yup, this is a huge problem with every version of XL (both Win and Mac).
The best utility I've used is FindLink.xla by MVP Bill Manville, which
you can get here:

http://www.bmsltd.co.uk/MVP/Default.htm


Often this is a problem with either corrupt files or with defined names.
Try FindLink, then see what the behavior is...

I have seen this problem a lot with workbooks that were originally created
in windows with active-X controls, and then were recoded to remove the
active-x controls for use on the MAC. When this happens, something
mysterious remains. I have not positively identified it, but I think it is
exacerbated when the active X controls were removed from Excel on the
Macintosh instead of on the PC. In any case the problem will probably go
away after you "clean" the code. After finding and removing the links. Copy
all the code to one or more text files, and then delete the modules. Save,
close and reopen the workbook. It wouldn't hurt to quit and restart Excel at
this point. Then copy the worksheets one at a time into a new workbook. Then
copy the code from the text files(s) back into appropriate places and new
modules in the new workbook. Save, close and try again.

 

[JE McGimpsey]

PMFJI

Never need P FJI with good stuff! <g>

 

[Geoff Lilley]

PMFJI, but I may be able to shed additional light on the problem which may
or may not help. See below


I have seen this problem a lot with workbooks that were originally created
in windows with active-X controls, and then were recoded to remove the
active-x controls for use on the MAC. When this happens, something
mysterious remains. I have not positively identified it, but I think it is
exacerbated when the active X controls were removed from Excel on the
Macintosh instead of on the PC. In any case the problem will probably go
away after you "clean" the code. After finding and removing the links. Copy
all the code to one or more text files, and then delete the modules. Save,
close and reopen the workbook. It wouldn't hurt to quit and restart Excel at
this point. Then copy the worksheets one at a time into a new workbook. Then
copy the code from the text files(s) back into appropriate places and new
modules in the new workbook. Save, close and try again.

Hope I'm not a johnny come lately on this one, but I hope this may help.
I've often phantom links to be from named ranges. When I create a
named range in Workbook A, then copy Worksheet 1 from Workbook A to
Workbook B, then I often find that ALL of the names from Workbook A come
over to Workbook B. Sometimes, going to Insert->Name->Define, and
looking for any weird range names can help resolve this.

 

[s4cinfo]

Thanks, everyone! Some great suggestions. I'll try them out. Stuart

PMFJI, but I may be able to shed additional light on the problem which may
or may not help. See below

On 4/17/07 4:20 PM, in article
(e-mail address removed), "JE McGimpsey"

 

[s4cinfo]

Thanks everyone. Great suggestions! I'll follow uop. Stuart