Select hidden sheets

[csilabgirl]

Excel 2002

I have a workbook with about 20 sheets in it. I want to be able to hide 10 of those sheets. I want those 10 sheets to be able to be access via a macro button but it seems when I use VBA code to hide them or use the format sheets function to hide them, I am unable to use code to select them i.e. sheets("TPA").select does not work.

Is there a way to make the sheet tabs on only 10 of 20 sheets invisible so that the user may only access those sheets through a macro.

Thank you for the help

 

[Smitty]

Right, you can't select a hidden sheet.

But you generally don't need to select objects in order to work with them:

Sheets("TPA").Visible = xlVeryHidden

Sheets("TPA").Visible = True

Using VeryHidden means that users can only use code to unhide them.

HTH,

 

[Rick Rothstein]

Excel 2002

I have a workbook with about 20 sheets in it. I want to be able to hide 10 of those sheets. I want those 10 sheets to be able to be access via a macro button but it seems when I use VBA code to hide them or use the format sheets function to hide them, I am unable to use code to select them i.e. sheets("TPA").select does not work.

You cannot select a cell on a sheet that is not active, but you rarely need to select cells to work with them. Consider this from a previous posting of mine...

Whenever you see code constructed like this...

Range("A1").Select
Selection.{whatever}<WHATEVER>

you can almost always do this instead...

Range("A1").{whatever}<WHATEVER>

In your particular case, you have this...

Range("C2:C8193").Select         'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use.

 

[csilabgirl]

yes I agree that you dont need to select sheets to work with them however the users need to be able to print the hidden sheets. The number of copies to print is variable and the printer needed is on a network so I wasnt sure how to create code to print a variable number of copies and send it to a network printer. So I need them to be able to be on the sheets so they can go to file, then print.

 

[Smitty]

Here's John Walkenbach's Print Dialog routine, which will create a user form with check boxes next to each sheet name. You could amend the code to unhide sheets, run, then rehide.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SelectSheets()<br>    <SPAN style="color:#007F00">'   John Walkenbach</SPAN><br>    <SPAN style="color:#007F00">'   www.j-walk.com</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> TopPos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> SheetCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> PrintDlg <SPAN style="color:#00007F">As</SPAN> DialogSheet<br>    <SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> cb <SPAN style="color:#00007F">As</SPAN> CheckBox<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#007F00">'   Check for protected workbook</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.ProtectStructure <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Workbook is protected.", vbCritical<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#007F00">'   Add a temporary dialog sheet</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveSheet<br>    <SPAN style="color:#00007F">Set</SPAN> PrintDlg = ActiveWorkbook.DialogSheets.Add<br><br>    SheetCount = 0<br><br><SPAN style="color:#007F00">'   Add the checkboxes</SPAN><br>    TopPos = 40<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ActiveWorkbook.Worksheets.Count<br>        <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveWorkbook.Worksheets(i)<br><SPAN style="color:#007F00">'       Skip empty sheets and hidden sheets</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Application.CountA(CurrentSheet.Cells) <> 0 And _<br>            CurrentSheet.Visible <SPAN style="color:#00007F">Then</SPAN><br>            SheetCount = SheetCount + 1<br>            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5<br>                PrintDlg.CheckBoxes(SheetCount).Text = _<br>                    CurrentSheet.Name<br>            TopPos = TopPos + 13<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#007F00">'   Move the OK and Cancel buttons</SPAN><br>    PrintDlg.Buttons.Left = 240<br><br><SPAN style="color:#007F00">'   Set dialog height, width, and caption</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> PrintDlg.DialogFrame<br>        .Height = Application.Max _<br>            (68, PrintDlg.DialogFrame.Top + TopPos - 34)<br>        .Width = 230<br>        .Caption = "Select sheets to print"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#007F00">'   Change tab order of OK and Cancel buttons</SPAN><br><SPAN style="color:#007F00">'   so the 1st option button will have the focus</SPAN><br>    PrintDlg.Buttons("Button 2").BringToFront<br>    PrintDlg.Buttons("Button 3").BringToFront<br><br><SPAN style="color:#007F00">'   Display the dialog box</SPAN><br>    CurrentSheet.Activate<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> SheetCount <> 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> PrintDlg.Show <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cb <SPAN style="color:#00007F">In</SPAN> PrintDlg.CheckBoxes<br>                <SPAN style="color:#00007F">If</SPAN> cb.Value = xlOn <SPAN style="color:#00007F">Then</SPAN><br>                    Worksheets(cb.Caption).Activate<br>                    ActiveSheet.PrintOut<br><SPAN style="color:#007F00">'                   ActiveSheet.PrintPreview 'for debugging</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> cb<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "All worksheets are empty."<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#007F00">'   Delete temporary dialog sheet (without a warning)</SPAN><br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    PrintDlg.Delete<br><br><SPAN style="color:#007F00">'   Reactivate original sheet</SPAN><br>    CurrentSheet.Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[csilabgirl]

wow thats some long code. Thank Smitty I will give it a try.