[ACCEPTED]-What Excel VBA actions are possible on hidden worksheets or workbooks?-excel
From the Visual Basic for Applications help:
When 21 an object is hidden, it's removed from the 20 screen and its Visible property is set to 19 False. A hidden object's controls aren't 18 accessible to the user, but they are available 17 programmatically to the running application, to 16 other processes that may be communicating 15 with the application through Automation, and 14 in Windows, to Timer control events.
Not 13 much help there I'm afraid, and I couldn't 12 find much else through Google.
As you said 11 yourself, the Select method and Selection 10 Property don't work on a hidden Worksheet, they 9 should work on a hidden Workbook though. (Please 8 correct me if I'm wrong.) In general however, it's 7 not always all that efficient to select 6 ranges in worksheets anyway, you are better 5 off working with the Range property (which 4 does work on a hidden worksheet).
The 3 following code will change the color of 2 A1:A8 to Cyan even when the Worksheet is 1 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
You can get around any limitations on hidden 8 sheets by unhiding them without the user 7 realizing it, doing whatever you need to, and 6 then hiding them again.
This example assumes 5 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 4 if you want your sheets hidden is to set 3 them to xlVeryHidden, which will prevent 2 them from being listed to the user if they 1 try to unhide them through the menu or ribbon.
More Related questions