Hello everyone,

I have a VBA routine in an addin that .Activates a hidden worksheet in
the user's workbook.

Is this an unwise thing to do?

Thanks,

Brian

I created a two worksheet (sheet1 and sheet2) workbook. I hid sheet2 and then
ran this:

Worksheets("Sheet2").Activate
Debug.Print ActiveSheet.Name

I saw this in the immediate window:
Sheet1

I added this:

Worksheets("Sheet2").Activate
ActiveSheet.Range("A1").Value = 999

And the 999 was added to Sheet1. I unhid sheet2 and A1 was empty.

I wouldn't activate a hidden sheet and expect it to work the way I want.

But there are not many things that you do in code that needs to work on the
activesheet. Maybe just working on the sheet directly would be sufficient in
your case:

with worksheets("Sheet2") 'still hidden
.range("a1").value = "some value"
end with



Brian Murphy wrote:
>
> Hello everyone,
>
> I have a VBA routine in an addin that .Activates a hidden worksheet in
> the user's workbook.
>
> Is this an unwise thing to do?
>
> Thanks,
>
> Brian

--

Dave Peterson

It doesn't cause an error but it doesn't do anything. If you activate a
hidden sheet, it does not change the sheet to which ActiveSheet refers. On
balance, it is probably an unwise thing to do, but how unwise depends on
what your code expects after it does activate the sheet.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"Brian Murphy" <(E-Mail Removed)> wrote in message
news:b9370f7a-dd3d-4086-917a-(E-Mail Removed)...
> Hello everyone,
>
> I have a VBA routine in an addin that .Activates a hidden worksheet in
> the user's workbook.
>
> Is this an unwise thing to do?
>
> Thanks,
>
> Brian

Thank you Dave & Chip for the replies. I feel privileged that two of
the group's top guys looked at this.

One thing I can say is that a .Activate statement to a hidden sheet
causes the current ActiveSheet to change to the last sheet in the file
(or maybe it's to a sheet next to the hidden sheet).

I've got Sheet .Activate statements in many places in many routines
(to both hidden and visible sheets). Perhaps they were necessary with
earlier Excel versions. Or maybe not.

I'm trying to figure out why a very old routine of mine (10+ years
old) is causing crashes in Excel 2003. Typically the macro runs with
no problem, but if a worksheet in the file is copied manually using
the mouse or Edit menu, then running the macro leads to a crash. Even
though the copied sheet has nothing to do with the macro.

Tonight, after yet another very long session on this, I've found that
a statement that puts a formula in a cell on a visible sheet is
closely related to the crashes.

I just tried unhiding the hidden sheets in the file (all two of them),
and it didn't make any difference. Actually, it might have made a
small difference. Instead of crashing the first or second time I run
the macro after a sheet copy, it might now take another run or two to
get a crash.

One thing that is definitely common to all the crashes - it has to be
after copying a sheet manually.

I think (hope) that I'll eventually figure this out, but it's sure
taking a long time. I've felt for many years that Excel has deep down
bugs in its code for copying sheets. So the resolution in this case
may be a workaround.

The crash behavior does not appear to happen with Excel 2007.

In my next session, I'm going to remove the sheet .Activate
statements, and double check that all range references are fully
qualified (I'm pretty sure they are, though).

Time for bed,

Brian

You may want to create a test workbook with just enough test data to make the
code work. Then try all your code to see if it works ok in that test workbook.

If it does, then you may want to consider recreating the real workbook.

It sounds to me that the original workbook may be corrupted.

I haven't had that much experience with really corrupted workbooks, but with one
troublesome workbook, I could move a worksheet to another workbook, but I
couldn't copy it to another workbook.

I got lucky and didn't have to recreate the entire workbook--just that problem
worksheet. And that was a pain!



Brian Murphy wrote:
>
> Thank you Dave & Chip for the replies. I feel privileged that two of
> the group's top guys looked at this.
>
> One thing I can say is that a .Activate statement to a hidden sheet
> causes the current ActiveSheet to change to the last sheet in the file
> (or maybe it's to a sheet next to the hidden sheet).
>
> I've got Sheet .Activate statements in many places in many routines
> (to both hidden and visible sheets). Perhaps they were necessary with
> earlier Excel versions. Or maybe not.
>
> I'm trying to figure out why a very old routine of mine (10+ years
> old) is causing crashes in Excel 2003. Typically the macro runs with
> no problem, but if a worksheet in the file is copied manually using
> the mouse or Edit menu, then running the macro leads to a crash. Even
> though the copied sheet has nothing to do with the macro.
>
> Tonight, after yet another very long session on this, I've found that
> a statement that puts a formula in a cell on a visible sheet is
> closely related to the crashes.
>
> I just tried unhiding the hidden sheets in the file (all two of them),
> and it didn't make any difference. Actually, it might have made a
> small difference. Instead of crashing the first or second time I run
> the macro after a sheet copy, it might now take another run or two to
> get a crash.
>
> One thing that is definitely common to all the crashes - it has to be
> after copying a sheet manually.
>
> I think (hope) that I'll eventually figure this out, but it's sure
> taking a long time. I've felt for many years that Excel has deep down
> bugs in its code for copying sheets. So the resolution in this case
> may be a workaround.
>
> The crash behavior does not appear to happen with Excel 2007.
>
> In my next session, I'm going to remove the sheet .Activate
> statements, and double check that all range references are fully
> qualified (I'm pretty sure they are, though).
>
> Time for bed,
>
> Brian

--

Dave Peterson

Dave,

I'm following your suggestion about a corrupt workbook. So far it's
certainly within the realm of possibility.

My excel app works with "user workbooks" that contain user inputs and
all results calculated by my app. I of course have lots of "user
workbooks," and so far I've found that only ones containing a certain
type of user data trigger the crash. So I've got a lead to follow.
Which is better than anything else I've come up with. I have rebuilt
a new workbook from a "crashing" workbook, but even the rebuilt file
triggers the crash. Darn! So I've got more work to do.

Brian

I don't know what caused the corruption in my worksheet.

But my gut feeling (no scientific testing here!) was that the worksheet got
corrupted because it had too many comments in it--and maybe because of all the
formatting.

After I recreated the worksheet, the formatting wasn't anywhere as complex and
the comments had been deleted.

I have no real idea if those things caused the corruption, though.

Brian Murphy wrote:
>
> Dave,
>
> I'm following your suggestion about a corrupt workbook. So far it's
> certainly within the realm of possibility.
>
> My excel app works with "user workbooks" that contain user inputs and
> all results calculated by my app. I of course have lots of "user
> workbooks," and so far I've found that only ones containing a certain
> type of user data trigger the crash. So I've got a lead to follow.
> Which is better than anything else I've come up with. I have rebuilt
> a new workbook from a "crashing" workbook, but even the rebuilt file
> triggers the crash. Darn! So I've got more work to do.
>
> Brian

--

Dave Peterson

I just did some testing on another computer with excel 2003-SP3. The
result is not good. I'm pretty much back to square one. I thought
the crash happened only with recent file versions containing certain
things. But this time I got the crash with old files.

I need to regroup and figure what to do next. The sequence of steps
required to get a crash are steps I take quite frequently when I used
the app for my own purposes, and year ago I could swear these crashes
weren't happening. I'm really stumped.

Brian

I don't have any more suggestions--except to rebuild a small test version and do
your testing. If it still crashes on that small test version, then at least you
didn't waste too much time rebuilding the entire workbook/worksheet.

Brian Murphy wrote:
>
> I just did some testing on another computer with excel 2003-SP3. The
> result is not good. I'm pretty much back to square one. I thought
> the crash happened only with recent file versions containing certain
> things. But this time I got the crash with old files.
>
> I need to regroup and figure what to do next. The sequence of steps
> required to get a crash are steps I take quite frequently when I used
> the app for my own purposes, and year ago I could swear these crashes
> weren't happening. I'm really stumped.
>
> Brian

--

Dave Peterson

Thanks for your help, Dave. It'll be two weeks before I can do any
more on this, and I won't have internet access, either. But then I'll
be determined to fix whatever it is.

Brian