Characteristics of an Invisible Sheet

[Cyberdude]

I just started playing with making a worksheet invisible, then making it reappear again. I'm having trouble finding a discussion of this property. It appears (so to speak) that a hidden sheet cannot be tampered with. For example, I discovered that after hiding a sheet, I was unable to ".Select" it. I presume that I also cannot write on it. So I guess that once a sheet is made "invisible", then it is out of the game until it is made visible again. Is that correct?

I run a series of macros each of which works on a workbook that has been opened. When near the end of execution, each macro in turn will open the next workbook, "Run" a macro in that workbook for initialization, then close the workbook to which it belongs.

This transition period involving the initialization causes a lot of flashing and brief sheet viewing as it changes from one workbook to another and does a few things to various sheets in the next workbook. I thought that maybe I could make the worksheets being initialized invisible and thereby reduce or eliminate the flashing and brief viewing of those sheets. It appears that I won't be able to do that once the sheet is hidden ... true??

[mdmackillop]

Hi Sid,
There are different degrees of invisibility
Sheets(1).Visible = False
Sheets(2).Visible = xlVeryHidden
which could also affect what you're trying to do.
Regards
Malcolm

[Cyberdude]

Hi, Malcolm! Thanks for the reply. I read the Help and it showed those states, but it didn't elaborate on what can and can't be done with a hidden sheet. The "xlVeryHidden" condition looked a tad scary, so I didn't mess with that. I just used ".Visible = False/True".

Is there also a way to make a window visible/invisible? Maybe that has some possibilities. I couldn't find anything about hiding windows, but I'm almost sure I saw someone do that somewhere.

I noticed that for one or two of the transitions I described above there are periods when my screen just goes blank while things are happening. I have no idea why that happens, but I'd like to cause it to happen at times.

[malik641]

Hey Sid,

Sure you can tamper with invisible sheets...for example:

[VBA]Option Explicit

Sub HideSheet()
Sheets(1).Visible = xlVeryHidden
MsgBox "Sheet3 Hidden"
Sheets(1).Range("A1") = "hi there"

Sheets(1).Visible = True
End Sub

[/VBA]
Not sure where your problem lies...unless that you try to select sheets in order to change values on them.

[Apps]

Hi Cyberdude,

You might also want to experiment with the trusty ScreenUpdating function which may well help with the epilepsy-causing-screen-flashing-syndrome that sometimes happens - this will effectively 'freeze' your screen whilst the code runs in the background.

[VBA]
Application.ScreenUpdating = False'<Turns it off

'Your code goes here

Application.ScreenUpdating = True'<Turns it back on
[/VBA]

Regards,
Apps

[lucas]

Hey Joseph,
shouldn't it be:
[VBA]
MsgBox "Sheet1 Hidden"
[/VBA]

[malik641]

Hey Joseph,
shouldn't it be:
[vba]
MsgBox "Sheet1 Hidden"
[/vba]

whooops

I originally had everything set to sheet 3, but I realized before I pressed "Post" that some users don't use all 3 sheets in a new workbook...so I thought that I changed everything to 1....oh well.

you knew what I meant

[lucas]

Yeah I knew, just couldn't resist...

[malik641]

ok, ok. Just make sure you're on your A-game. I'll be watching you lol