What Excel VBA actions are possible on hidden worksheets or workbooks?


What Excel VBA actions are possible on hidden worksheets or workbooks?



Hidden worksheets/workbooks have some limitations to what can be done in VBA code, like most Select and Selection statements, and anything coming from ActiveSheet, but I can't seem to find any list of what the limitations are.

Google, the built-in documentation in the help system, and MSDN's website have all failed me. Can anyone point me in the right direction?

Edit: The workbook is opened with

Set WB_Master = Workbooks.Open(Filename:=PATH_Master, ReadOnly:=False) 

and then hidden with

WB_Master.Windows(1).Visible = False 



Excel import returns blank cells

1:



Using CreateObject(“Excel.Application”) - issues with unsigned control
From the Visual Basic for Applications help:.
Using .Find to look in another worksheet
When an object is hidden, it's removed from the screen and its Visible property is set to False.


Using Range Values with Find
A hidden object's controls aren't accessible to the user, but they are available programmatically to the running application, to other processes that may be communicating with the application through Automation, and in Windows, to Timer control events..
Show Cell Range on UserForm; then update
Not much help there I'm afraid, and I couldn't find much else through Google..
Error: “Could Not Find Installable ISAM”
As you said yourself, the Select method and Selection Property don't work on a hidden Worksheet, they should work on a hidden Workbook though.


Non-web SQL Injection
(Please correct me if I'm wrong.) In general however, it's not always all that efficient to select ranges in worksheets anyway, you are better off working with the Range property (which does work on a hidden worksheet)..
Non-Sequential Range
EDIT:. The following code will change the color of A1:A8 to Cyan even when the Worksheet is not visible:.
Dim book2 As Workbook Set book2 = Workbooks.Open("C:\Book2.xls")  book2.Worksheets("Sheet1").Visible = False book2.Windows(1).Visible = False  With book2.Worksheets("Sheet1").Range("A1:E8").Interior     .ColorIndex = 8     .Pattern = xlSolid     .PatternColorIndex = xlAutomatic End With  book2.Windows(1).Visible = True book2.Worksheets("Sheet1").Visible = True 


2:


You can get around any limitations on hidden sheets by unhiding them without the user realizing it, doing whatever you need to, and then hiding them again.. This example assumes that Sheet2 is hidden..
Sub DoStuffToAHiddenSheetWithoutTheUserKnowingIt()     'turns off screen repainting so the user can't see what you're doing     'incidentally, this dramatically speeds up processing of your code     Application.ScreenUpdating = False     'note that if you're stepping through your code, screenupdating will be true anyway      'unhide the sheet you want to work with     Sheets("sheet2").Visible = True         'do whatever you want here, including selecting cells if you want         'Scagnelli is right though, only select cells if you have to      'when you're finished, hide the sheet again     Sheets("sheet2").Visible = False      'make sure you turn screenupdating back on, or Excel will be useless     Application.ScreenUpdating = True End Sub 
Another useful trick if you want your sheets hidden is to set them to xlVeryHidden, which will prevent them from being listed to the user if they try to unhide them through the menu or ribbon..



73 out of 100 based on 73 user ratings 1273 reviews

@