MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Making changes to a hidden worksheet


Posted by Stan on December 01, 2001 12:53 PM

Earlier I posted a message about an application execution error I was getting when closing an Excel VBA file. Here's what I've since learned:

The file has all but one worksheet hidden. If I make change programatically to a hidden worksheet (i.e. change the value of a cell), I get an error when exiting the file. But if the sheet is not hidden when I make the change, there is no problem. Any idea what's happening?


Posted by Voltimand on December 01, 2001 1:50 PM


This doesn't happen to me . What does your code look like?

Posted by Stan on December 01, 2001 2:31 PM

Volltimand

The code is pretty complex to describe here. Essentially what I have to do to make it work is use the following:

Private Sub UserForm_Activate()

Sheets("MySheets").Visible = True

'rest of code

End Sub

This opens the Worksheet and I can make changes to it programatically. I tried using Sheets("MySheets").Activate instead but that didn't work. I'm not sure what "Activate" is suppose to do - set the focus? Do you?

Stan

Posted by Voltimand on December 01, 2001 2:42 PM

Sorry, but cannot reproduce your problem.
Am able to programmatically change hidden worksheets without any problem.
Perhaps someone else can help.

Posted by bruno on December 02, 2001 12:12 AM

Hello, I am also able to change a hidden worksheet...
When you only don't want to see the worksheet, maybe you can use :
Application.ScreenUpdating = False
Sheets("MySheets").Visible = True
'make your changes
Sheets("MySheets").Visible = False
Application.ScreenUpdating = True

It will take a split second.

Otherwise... is the worksheet protected ?

Bruno Sorry, but cannot reproduce your problem.

Posted by Stan on December 02, 2001 7:48 AM

My problem is not that I can't make changes to a hidden worksheet - its when I make a change and then exit the Workbook, the application hangs up. But if I have the worksheet visible when I make the change I have no problem exiting the application. This is baffling me to no end.

Posted by Oscar Isasmendi on December 05, 2001 12:12 PM

Hi,
I'm just wandering if your code selects some range from the hiddend worksheets. Try unhidding them, then walk through the code to see if it happens.
Hope it helps. ;)