| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Abdul
Guest
Posts: n/a
|
Can you make sheets invisible and if so, how can you recall them [make them
visible again]? |
|
||
|
||||
|
|
|
| |
|
Chip Pearson
Guest
Posts: n/a
|
To hide a sheet, go to the Format menu, choose Worksheet, and then select Hide. To make a hidden sheet visible, go to the Format menu, choose Worksheet, and then select UnHide. You can do this with code in a manner similar to the following: ' hide the sheets ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden ' unhide the sheets ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible ' hide all but active sheet Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then WS.Visible = xlSheetHidden End If Next WS ' unhide all sheets For Each WS In ThisWorkbook.Worksheets WS.Visible = xlSheetVisible Next WS When the Visible property is xlVisible, the sheet is visible. When the Visible property is xlHidden, the sheet is hidden but can be made visible from the Format menu. When the Visible property is xlVeryHidden, the sheet is hidden and cannot be made visible from the Format menu. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 29 Nov 2008 07:46:01 -0800, Abdul <(E-Mail Removed)> wrote: >Can you make sheets invisible and if so, how can you recall them [make them >visible again]? |
|
||
|
|
||||
|
Shane Devenshire
Guest
Posts: n/a
|
Hi,
One point - you can't hide all of the sheets in a workbook, one must be visible. Although you can hide the workbook. In 2007 the command is Home, Format, Hide & Unhide, Hide Sheet. If this helps, please click the Yes button Cheers, Shane Devenshire "Abdul" wrote: > Can you make sheets invisible and if so, how can you recall them [make them > visible again]? |
|
||
|
|
||||
|
John
Guest
Posts: n/a
|
Hi Chip
I installed the codes for Very Hidden and works fine but to unhide I get an error " Unable to set the visible property of the worksheet class" this error is in VB editor runing the macro with F5 in the worksheet I only get a Popup window with the number 400 nothing else Regards John There is no failure except in no longer trying. Elbert Hubbard "Chip Pearson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > > To hide a sheet, go to the Format menu, choose Worksheet, and then > select Hide. To make a hidden sheet visible, go to the Format menu, > choose Worksheet, and then select UnHide. > > You can do this with code in a manner similar to the following: > > ' hide the sheets > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden > > ' unhide the sheets > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible > > ' hide all but active sheet > Dim WS As Worksheet > For Each WS In ThisWorkbook.Worksheets > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then > WS.Visible = xlSheetHidden > End If > Next WS > > ' unhide all sheets > For Each WS In ThisWorkbook.Worksheets > WS.Visible = xlSheetVisible > Next WS > > > When the Visible property is xlVisible, the sheet is visible. When the > Visible property is xlHidden, the sheet is hidden but can be made > visible from the Format menu. When the Visible property is > xlVeryHidden, the sheet is hidden and cannot be made visible from the > Format menu. > > Cordially, > Chip Pearson > Microsoft MVP > Excel Product Group > Pearson Software Consulting, LLC > www.cpearson.com > (email on web site) > > > > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul > <(E-Mail Removed)> wrote: > >>Can you make sheets invisible and if so, how can you recall them [make >>them >>visible again]? |
|
||
|
|
||||
|
Dave Peterson
Guest
Posts: n/a
|
Did you protect the workbook (not the worksheet)?
If so, then unprotect the workbook first. If no, you may want to share the code you're using to show the worksheets. John wrote: > > Hi Chip > I installed the codes for Very Hidden and works fine but to unhide I get > an error " Unable to set the visible property of the worksheet class" > this error is in VB editor runing the macro with F5 in the worksheet I > only get a Popup window with the number 400 nothing else > Regards > John > There is no failure except in no longer trying. > Elbert Hubbard > > "Chip Pearson" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... > > > > To hide a sheet, go to the Format menu, choose Worksheet, and then > > select Hide. To make a hidden sheet visible, go to the Format menu, > > choose Worksheet, and then select UnHide. > > > > You can do this with code in a manner similar to the following: > > > > ' hide the sheets > > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden > > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden > > > > ' unhide the sheets > > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible > > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible > > > > ' hide all but active sheet > > Dim WS As Worksheet > > For Each WS In ThisWorkbook.Worksheets > > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then > > WS.Visible = xlSheetHidden > > End If > > Next WS > > > > ' unhide all sheets > > For Each WS In ThisWorkbook.Worksheets > > WS.Visible = xlSheetVisible > > Next WS > > > > > > When the Visible property is xlVisible, the sheet is visible. When the > > Visible property is xlHidden, the sheet is hidden but can be made > > visible from the Format menu. When the Visible property is > > xlVeryHidden, the sheet is hidden and cannot be made visible from the > > Format menu. > > > > Cordially, > > Chip Pearson > > Microsoft MVP > > Excel Product Group > > Pearson Software Consulting, LLC > > www.cpearson.com > > (email on web site) > > > > > > > > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul > > <(E-Mail Removed)> wrote: > > > >>Can you make sheets invisible and if so, how can you recall them [make > >>them > >>visible again]? -- Dave Peterson |
|
||
|
|
||||
|
John
Guest
Posts: n/a
|
Hi Dave
Thank you for your reply The workbook is not hiden and I used the same line as below to hide it ("Very hidden")The macro was places in sheet2 not workbook but also tried in the module, Here is the line code: Sub Visible() ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible End Sub Regards John "Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > Did you protect the workbook (not the worksheet)? > > If so, then unprotect the workbook first. > > If no, you may want to share the code you're using to show the worksheets. > > John wrote: >> >> Hi Chip >> I installed the codes for Very Hidden and works fine but to unhide I get >> an error " Unable to set the visible property of the worksheet class" >> this error is in VB editor runing the macro with F5 in the worksheet I >> only get a Popup window with the number 400 nothing else >> Regards >> John >> There is no failure except in no longer trying. >> Elbert Hubbard >> >> "Chip Pearson" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >> > >> > To hide a sheet, go to the Format menu, choose Worksheet, and then >> > select Hide. To make a hidden sheet visible, go to the Format menu, >> > choose Worksheet, and then select UnHide. >> > >> > You can do this with code in a manner similar to the following: >> > >> > ' hide the sheets >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden >> > >> > ' unhide the sheets >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible >> > >> > ' hide all but active sheet >> > Dim WS As Worksheet >> > For Each WS In ThisWorkbook.Worksheets >> > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then >> > WS.Visible = xlSheetHidden >> > End If >> > Next WS >> > >> > ' unhide all sheets >> > For Each WS In ThisWorkbook.Worksheets >> > WS.Visible = xlSheetVisible >> > Next WS >> > >> > >> > When the Visible property is xlVisible, the sheet is visible. When the >> > Visible property is xlHidden, the sheet is hidden but can be made >> > visible from the Format menu. When the Visible property is >> > xlVeryHidden, the sheet is hidden and cannot be made visible from the >> > Format menu. >> > >> > Cordially, >> > Chip Pearson >> > Microsoft MVP >> > Excel Product Group >> > Pearson Software Consulting, LLC >> > www.cpearson.com >> > (email on web site) >> > >> > >> > >> > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul >> > <(E-Mail Removed)> wrote: >> > >> >>Can you make sheets invisible and if so, how can you recall them [make >> >>them >> >>visible again]? > > -- > > Dave Peterson |
|
||
|
|
||||
|
Dave Peterson
Guest
Posts: n/a
|
It's not the visibility of the workbook that's important. It's whether the
workbook is protected. In xl2003 menus: Tools|Protection Do you see "Unprotect Workbook" as an option? If you do, then you have to unprotect the workbook before you unhide a worksheet. This code belongs in a general module--not behind a worksheet and not behind the ThisWorkbook module. And by using ThisWorkbook, that means that the workbook containing the code is the workbook that should have Sheet3 made visible. ps. I wouldn't name the subroutine visible, I'd use a different name. Visible is the name of a property that VBA owns. It may not confuse excel, but it could confuse me. Sub MacVisible() ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible 'or maybe 'Activeworkbook.Worksheets("Sheet3").Visible = xlVisible End Sub John wrote: > > Hi Dave > Thank you for your reply > The workbook is not hiden and I used the same line as below to hide it > ("Very hidden")The macro was places in sheet2 not workbook but also tried in > the module, > Here is the line code: > Sub Visible() > ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible > End Sub > Regards > John > > "Dave Peterson" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... > > Did you protect the workbook (not the worksheet)? > > > > If so, then unprotect the workbook first. > > > > If no, you may want to share the code you're using to show the worksheets. > > > > John wrote: > >> > >> Hi Chip > >> I installed the codes for Very Hidden and works fine but to unhide I get > >> an error " Unable to set the visible property of the worksheet class" > >> this error is in VB editor runing the macro with F5 in the worksheet I > >> only get a Popup window with the number 400 nothing else > >> Regards > >> John > >> There is no failure except in no longer trying. > >> Elbert Hubbard > >> > >> "Chip Pearson" <(E-Mail Removed)> wrote in message > >> news:(E-Mail Removed)... > >> > > >> > To hide a sheet, go to the Format menu, choose Worksheet, and then > >> > select Hide. To make a hidden sheet visible, go to the Format menu, > >> > choose Worksheet, and then select UnHide. > >> > > >> > You can do this with code in a manner similar to the following: > >> > > >> > ' hide the sheets > >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden > >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden > >> > > >> > ' unhide the sheets > >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible > >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible > >> > > >> > ' hide all but active sheet > >> > Dim WS As Worksheet > >> > For Each WS In ThisWorkbook.Worksheets > >> > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then > >> > WS.Visible = xlSheetHidden > >> > End If > >> > Next WS > >> > > >> > ' unhide all sheets > >> > For Each WS In ThisWorkbook.Worksheets > >> > WS.Visible = xlSheetVisible > >> > Next WS > >> > > >> > > >> > When the Visible property is xlVisible, the sheet is visible. When the > >> > Visible property is xlHidden, the sheet is hidden but can be made > >> > visible from the Format menu. When the Visible property is > >> > xlVeryHidden, the sheet is hidden and cannot be made visible from the > >> > Format menu. > >> > > >> > Cordially, > >> > Chip Pearson > >> > Microsoft MVP > >> > Excel Product Group > >> > Pearson Software Consulting, LLC > >> > www.cpearson.com > >> > (email on web site) > >> > > >> > > >> > > >> > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul > >> > <(E-Mail Removed)> wrote: > >> > > >> >>Can you make sheets invisible and if so, how can you recall them [make > >> >>them > >> >>visible again]? > > > > -- > > > > Dave Peterson -- Dave Peterson |
|
||
|
|
||||
|
John
Guest
Posts: n/a
|
Hi Dave
Thanks again for your patient. I deleted the problem file and started with a new one. I copied what you see below in a module. It will hide ok but will not unhide, still getting the same error. Regards John ....................................................... Sub testhide() ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden End Sub ..................................................... Sub testunhide() 'ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible 'or maybe ActiveWorkbook.Worksheets("Sheet2").Visible = xlVisible End Sub ...................................................... "Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > It's not the visibility of the workbook that's important. It's whether > the > workbook is protected. > > In xl2003 menus: > Tools|Protection > Do you see "Unprotect Workbook" as an option? If you do, then you have to > unprotect the workbook before you unhide a worksheet. > > This code belongs in a general module--not behind a worksheet and not > behind the > ThisWorkbook module. > > And by using ThisWorkbook, that means that the workbook containing the > code is > the workbook that should have Sheet3 made visible. > > ps. I wouldn't name the subroutine visible, I'd use a different name. > Visible > is the name of a property that VBA owns. It may not confuse excel, but it > could > confuse me. > > Sub MacVisible() > ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible > 'or maybe > 'Activeworkbook.Worksheets("Sheet3").Visible = xlVisible > End Sub > > > John wrote: >> >> Hi Dave >> Thank you for your reply >> The workbook is not hiden and I used the same line as below to hide it >> ("Very hidden")The macro was places in sheet2 not workbook but also tried >> in >> the module, >> Here is the line code: >> Sub Visible() >> ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible >> End Sub >> Regards >> John >> >> "Dave Peterson" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >> > Did you protect the workbook (not the worksheet)? >> > >> > If so, then unprotect the workbook first. >> > >> > If no, you may want to share the code you're using to show the >> > worksheets. >> > >> > John wrote: >> >> >> >> Hi Chip >> >> I installed the codes for Very Hidden and works fine but to unhide I >> >> get >> >> an error " Unable to set the visible property of the worksheet class" >> >> this error is in VB editor runing the macro with F5 in the worksheet I >> >> only get a Popup window with the number 400 nothing else >> >> Regards >> >> John >> >> There is no failure except in no longer trying. >> >> Elbert Hubbard >> >> >> >> "Chip Pearson" <(E-Mail Removed)> wrote in message >> >> news:(E-Mail Removed)... >> >> > >> >> > To hide a sheet, go to the Format menu, choose Worksheet, and then >> >> > select Hide. To make a hidden sheet visible, go to the Format menu, >> >> > choose Worksheet, and then select UnHide. >> >> > >> >> > You can do this with code in a manner similar to the following: >> >> > >> >> > ' hide the sheets >> >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden >> >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden >> >> > >> >> > ' unhide the sheets >> >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible >> >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible >> >> > >> >> > ' hide all but active sheet >> >> > Dim WS As Worksheet >> >> > For Each WS In ThisWorkbook.Worksheets >> >> > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then >> >> > WS.Visible = xlSheetHidden >> >> > End If >> >> > Next WS >> >> > >> >> > ' unhide all sheets >> >> > For Each WS In ThisWorkbook.Worksheets >> >> > WS.Visible = xlSheetVisible >> >> > Next WS >> >> > >> >> > >> >> > When the Visible property is xlVisible, the sheet is visible. When >> >> > the >> >> > Visible property is xlHidden, the sheet is hidden but can be made >> >> > visible from the Format menu. When the Visible property is >> >> > xlVeryHidden, the sheet is hidden and cannot be made visible from >> >> > the >> >> > Format menu. >> >> > >> >> > Cordially, >> >> > Chip Pearson >> >> > Microsoft MVP >> >> > Excel Product Group >> >> > Pearson Software Consulting, LLC >> >> > www.cpearson.com >> >> > (email on web site) >> >> > >> >> > >> >> > >> >> > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul >> >> > <(E-Mail Removed)> wrote: >> >> > >> >> >>Can you make sheets invisible and if so, how can you recall them >> >> >>[make >> >> >>them >> >> >>visible again]? >> > >> > -- >> > >> > Dave Peterson > > -- > > Dave Peterson |
|
||
|
|
||||
|
John
Guest
Posts: n/a
|
Me again
I should of said also that I tried both lines to unhide without success. john "John" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > Hi Dave > Thanks again for your patient. > I deleted the problem file and started with a new one. > I copied what you see below in a module. > It will hide ok but will not unhide, still getting the same error. > Regards > John > ...................................................... > Sub testhide() > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden > > End Sub > .................................................... > Sub testunhide() > > 'ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible > 'or maybe > ActiveWorkbook.Worksheets("Sheet2").Visible = xlVisible > End Sub > ..................................................... > > "Dave Peterson" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> It's not the visibility of the workbook that's important. It's whether >> the >> workbook is protected. >> >> In xl2003 menus: >> Tools|Protection >> Do you see "Unprotect Workbook" as an option? If you do, then you have >> to >> unprotect the workbook before you unhide a worksheet. >> >> This code belongs in a general module--not behind a worksheet and not >> behind the >> ThisWorkbook module. >> >> And by using ThisWorkbook, that means that the workbook containing the >> code is >> the workbook that should have Sheet3 made visible. >> >> ps. I wouldn't name the subroutine visible, I'd use a different name. >> Visible >> is the name of a property that VBA owns. It may not confuse excel, but >> it could >> confuse me. >> >> Sub MacVisible() >> ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible >> 'or maybe >> 'Activeworkbook.Worksheets("Sheet3").Visible = xlVisible >> End Sub >> >> >> John wrote: >>> >>> Hi Dave >>> Thank you for your reply >>> The workbook is not hiden and I used the same line as below to hide it >>> ("Very hidden")The macro was places in sheet2 not workbook but also >>> tried in >>> the module, >>> Here is the line code: >>> Sub Visible() >>> ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible >>> End Sub >>> Regards >>> John >>> >>> "Dave Peterson" <(E-Mail Removed)> wrote in message >>> news:(E-Mail Removed)... >>> > Did you protect the workbook (not the worksheet)? >>> > >>> > If so, then unprotect the workbook first. >>> > >>> > If no, you may want to share the code you're using to show the >>> > worksheets. >>> > >>> > John wrote: >>> >> >>> >> Hi Chip >>> >> I installed the codes for Very Hidden and works fine but to unhide I >>> >> get >>> >> an error " Unable to set the visible property of the worksheet class" >>> >> this error is in VB editor runing the macro with F5 in the worksheet >>> >> I >>> >> only get a Popup window with the number 400 nothing else >>> >> Regards >>> >> John >>> >> There is no failure except in no longer trying. >>> >> Elbert Hubbard >>> >> >>> >> "Chip Pearson" <(E-Mail Removed)> wrote in message >>> >> news:(E-Mail Removed)... >>> >> > >>> >> > To hide a sheet, go to the Format menu, choose Worksheet, and then >>> >> > select Hide. To make a hidden sheet visible, go to the Format menu, >>> >> > choose Worksheet, and then select UnHide. >>> >> > >>> >> > You can do this with code in a manner similar to the following: >>> >> > >>> >> > ' hide the sheets >>> >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden >>> >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden >>> >> > >>> >> > ' unhide the sheets >>> >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible >>> >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible >>> >> > >>> >> > ' hide all but active sheet >>> >> > Dim WS As Worksheet >>> >> > For Each WS In ThisWorkbook.Worksheets >>> >> > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then >>> >> > WS.Visible = xlSheetHidden >>> >> > End If >>> >> > Next WS >>> >> > >>> >> > ' unhide all sheets >>> >> > For Each WS In ThisWorkbook.Worksheets >>> >> > WS.Visible = xlSheetVisible >>> >> > Next WS >>> >> > >>> >> > >>> >> > When the Visible property is xlVisible, the sheet is visible. When >>> >> > the >>> >> > Visible property is xlHidden, the sheet is hidden but can be made >>> >> > visible from the Format menu. When the Visible property is >>> >> > xlVeryHidden, the sheet is hidden and cannot be made visible from >>> >> > the >>> >> > Format menu. >>> >> > >>> >> > Cordially, >>> >> > Chip Pearson >>> >> > Microsoft MVP >>> >> > Excel Product Group >>> >> > Pearson Software Consulting, LLC >>> >> > www.cpearson.com >>> >> > (email on web site) >>> >> > >>> >> > >>> >> > >>> >> > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul >>> >> > <(E-Mail Removed)> wrote: >>> >> > >>> >> >>Can you make sheets invisible and if so, how can you recall them >>> >> >>[make >>> >> >>them >>> >> >>visible again]? >>> > >>> > -- >>> > >>> > Dave Peterson >> >> -- >> >> Dave Peterson > |
|
||
|
|
||||
|
Dave Peterson
Guest
Posts: n/a
|
Sorry, there was a typo in the original that I didn't notice.
Instead of using xlvisible, use: xlSheetVisible ActiveWorkbook.Worksheets("Sheet2").Visible = xlSheetVisible Remember, ActiveWorkbook refers to the workbook that's currently active. ThisWorkbook refers to the workbook that owns the code. They don't have to be the same workbook. John wrote: > > Me again > I should of said also that I tried both lines to unhide without success. > john > "John" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... > > Hi Dave > > Thanks again for your patient. > > I deleted the problem file and started with a new one. > > I copied what you see below in a module. > > It will hide ok but will not unhide, still getting the same error. > > Regards > > John > > ...................................................... > > Sub testhide() > > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden > > > > End Sub > > .................................................... > > Sub testunhide() > > > > 'ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible > > 'or maybe > > ActiveWorkbook.Worksheets("Sheet2").Visible = xlVisible > > End Sub > > ..................................................... > > > > "Dave Peterson" <(E-Mail Removed)> wrote in message > > news:(E-Mail Removed)... > >> It's not the visibility of the workbook that's important. It's whether > >> the > >> workbook is protected. > >> > >> In xl2003 menus: > >> Tools|Protection > >> Do you see "Unprotect Workbook" as an option? If you do, then you have > >> to > >> unprotect the workbook before you unhide a worksheet. > >> > >> This code belongs in a general module--not behind a worksheet and not > >> behind the > >> ThisWorkbook module. > >> > >> And by using ThisWorkbook, that means that the workbook containing the > >> code is > >> the workbook that should have Sheet3 made visible. > >> > >> ps. I wouldn't name the subroutine visible, I'd use a different name. > >> Visible > >> is the name of a property that VBA owns. It may not confuse excel, but > >> it could > >> confuse me. > >> > >> Sub MacVisible() > >> ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible > >> 'or maybe > >> 'Activeworkbook.Worksheets("Sheet3").Visible = xlVisible > >> End Sub > >> > >> > >> John wrote: > >>> > >>> Hi Dave > >>> Thank you for your reply > >>> The workbook is not hiden and I used the same line as below to hide it > >>> ("Very hidden")The macro was places in sheet2 not workbook but also > >>> tried in > >>> the module, > >>> Here is the line code: > >>> Sub Visible() > >>> ThisWorkbook.Worksheets("Sheet3").Visible = xlVisible > >>> End Sub > >>> Regards > >>> John > >>> > >>> "Dave Peterson" <(E-Mail Removed)> wrote in message > >>> news:(E-Mail Removed)... > >>> > Did you protect the workbook (not the worksheet)? > >>> > > >>> > If so, then unprotect the workbook first. > >>> > > >>> > If no, you may want to share the code you're using to show the > >>> > worksheets. > >>> > > >>> > John wrote: > >>> >> > >>> >> Hi Chip > >>> >> I installed the codes for Very Hidden and works fine but to unhide I > >>> >> get > >>> >> an error " Unable to set the visible property of the worksheet class" > >>> >> this error is in VB editor runing the macro with F5 in the worksheet > >>> >> I > >>> >> only get a Popup window with the number 400 nothing else > >>> >> Regards > >>> >> John > >>> >> There is no failure except in no longer trying. > >>> >> Elbert Hubbard > >>> >> > >>> >> "Chip Pearson" <(E-Mail Removed)> wrote in message > >>> >> news:(E-Mail Removed)... > >>> >> > > >>> >> > To hide a sheet, go to the Format menu, choose Worksheet, and then > >>> >> > select Hide. To make a hidden sheet visible, go to the Format menu, > >>> >> > choose Worksheet, and then select UnHide. > >>> >> > > >>> >> > You can do this with code in a manner similar to the following: > >>> >> > > >>> >> > ' hide the sheets > >>> >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlHidden > >>> >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVeryHidden > >>> >> > > >>> >> > ' unhide the sheets > >>> >> > ThisWorkbook.Worksheets("Sheet1").Visible = xlVisible > >>> >> > ThisWorkbook.Worksheets("Sheet2").Visible = xlVisible > >>> >> > > >>> >> > ' hide all but active sheet > >>> >> > Dim WS As Worksheet > >>> >> > For Each WS In ThisWorkbook.Worksheets > >>> >> > If StrComp(ActiveSheet.Name, WS.Name, vbBinaryCompare) <> 0 Then > >>> >> > WS.Visible = xlSheetHidden > >>> >> > End If > >>> >> > Next WS > >>> >> > > >>> >> > ' unhide all sheets > >>> >> > For Each WS In ThisWorkbook.Worksheets > >>> >> > WS.Visible = xlSheetVisible > >>> >> > Next WS > >>> >> > > >>> >> > > >>> >> > When the Visible property is xlVisible, the sheet is visible. When > >>> >> > the > >>> >> > Visible property is xlHidden, the sheet is hidden but can be made > >>> >> > visible from the Format menu. When the Visible property is > >>> >> > xlVeryHidden, the sheet is hidden and cannot be made visible from > >>> >> > the > >>> >> > Format menu. > >>> >> > > >>> >> > Cordially, > >>> >> > Chip Pearson > >>> >> > Microsoft MVP > >>> >> > Excel Product Group > >>> >> > Pearson Software Consulting, LLC > >>> >> > www.cpearson.com > >>> >> > (email on web site) > >>> >> > > >>> >> > > >>> >> > > >>> >> > On Sat, 29 Nov 2008 07:46:01 -0800, Abdul > >>> >> > <(E-Mail Removed)> wrote: > >>> >> > > >>> >> >>Can you make sheets invisible and if so, how can you recall them > >>> >> >>[make > >>> >> >>them > >>> >> >>visible again]? > >>> > > >>> > -- > >>> > > >>> > Dave Peterson > >> > >> -- > >> > >> Dave Peterson > > -- Dave Peterson |
|
||
|
|
||||
|
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Hide sheets using vba | PJ | Microsoft Excel Discussion | 3 | 23rd Sep 2006 11:41 AM |
| Hide all Sheets | Roba1 | Microsoft Excel Programming | 4 | 7th Jun 2006 02:41 PM |
| Hide all sheets but selected sheets - an example | aztecbrainsurgeon@yahoo.com | Microsoft Excel Programming | 1 | 7th Apr 2006 06:29 PM |
| Hide all Sheets | Fable | Microsoft Excel Misc | 0 | 18th Nov 2004 05:49 PM |
| Hide all Sheets | Fable | Microsoft Excel Misc | 1 | 18th Nov 2004 05:28 PM |
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc. |
