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. ;)
