Strange behavior

[Greg Lovern]

We have a long series of macros that create certain workbooks which
also have macros. If a user opens those created workbooks with macros
disabled, and if Application.EnableEvents also happens to be turned
off for that user, then the worksheet area does not display.

They can see the workbook name in Excel's title bar, and they can see
the workbook listed in Switch Windows, and they can see the tabs and
tab names. They just can't see any of the cells. If another workbook
was open, they still see that workbook's cells.

If they just click on any of the tabs, then the worksheet area
appears, and all is well. If they save the workbook (even without
making any changes), then it works normally after that.

Any ideas about why that might be happening?


Thanks,

Greg

 

[Greg Lovern]

Here's the solution I found in case anyone else ever runs into the
same problem:

First, I isolated the problem to three lines that cleared the contents
(.Value = "") of three cells. That surprised me because the code does
a lot of that earlier on, but the problem doesn't occur until these 3
lines. The only thing different about these three lines is that the
contents of the cells before running those lines are hyperlinks. The
problem occurrred even if I ran only one of those lines.

The problem was fixed by closing and reopening the target workbook
right before running those lines. I have no idea why that was needed.


Greg

 

[Rick Rothstein]

First, I isolated the problem to three lines that

cleared the contents (.Value = "") of three cells.

Rather than assigning the empty string to the Value property, a better way
to clear the contents of a cell would be to use the ClearContents property
instead...

.ClearContents

Whether that will solve your problem or not, I don't know (hyperlinks are
more of an object than they are a value). If you don't mind deleting
formatting and all, you could just use the Clear property...

.Clear

and that should solve your problem. If you want to stick with your assigning
the empty string to the Value property, you might first try deleting the
hyperlink to see if that solves your problem...

.Hyperlinks(1).Delete
.Value = ""

Although I would still suggest using the Clear property in place of the
empty string assignment.

Rick Rothstein (MVP - Excel)

 

[Greg Lovern]

Hi Rick,

Thanks, but I went back to the old version with the problem and tried
your fixes, and they did not work.

First I tried:

..ClearContents


Then I tried:

..Hyperlinks(1).Delete
..ClearContents


Then I tried:

..Hyperlinks(1).Delete
..Clear


Then I tried:

..Clear


None of them fixed the problem.

However, on my fixed version (the one that works around the problem by
closing and reopening the target workbook right before the problem
lines), I've changed the problem lines to use ClearContents, since
that is good hygiene.


Thanks,

Greg