Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting veryHidden worksheets
Can a worksheet which is very hidden be deleted without being made visible
first? I can manipulate a veryhidden sheet using an object variable, but I either can't get the syntax right to delete it or there is some other problem beyond my limited knowledge. Can someone please shed some light on how to delete such sheets? What I have tried so far: (it fails with a message telling me that I cannot have duplicate names; if I exit the routine after the Worksheets(WBSsht).Delete line, then the sheet is still present under MS Objects. Public WBSsht As Object Option Explicit Sub procMain() Dim sh As Worksheet Dim rngPlaceHolder As Range 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") Set sh = WBSsht If sh Is Nothing Then 'Doesn't exist Set sh = Nothing On Error GoTo 0 Else 'Does exist - delete it before proceeding Set sh = Nothing 'Application.DisplayAlerts = False Worksheets(WBSsht).Delete 'Application.DisplayAlerts = True On Error GoTo 0 End If Set WBSsht = Worksheets.Add 'and give it a name WBSsht.Name = ("WBSlist") Worksheets("WBSlist").Visible = xlVeryHidden End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting veryHidden worksheets
Interesting problem; I tried to come up with a solution (unsuccessfully) but
maybe these will give you additional ideas. In Excel 2003 I tried screenupdating=false, but the sheet still appeared when I set it to visible before deleting it. I also tried changing the visibility from veryhidden to just hidden, then deleting the sheet, and it crashed Excel 2003. On recovering the document, I see that sheet is visible in the VBA project pane, but is no longer a worksheet (it has the same icon as "ThisWorkbook" instead of the other sheets). Is it critical that the sheet not be seen at all (not even the sheet tab) or do you just need to hide the sheet contents? I'd think that as long as another sheet is active, you could unhide your target sheet and delete it, and the only visual indication would be the (very brief) appearance of a worksheet tab. Sorry I don't have a complete solution, Keith "Project Mangler" wrote: Can a worksheet which is very hidden be deleted without being made visible first? I can manipulate a veryhidden sheet using an object variable, but I either can't get the syntax right to delete it or there is some other problem beyond my limited knowledge. Can someone please shed some light on how to delete such sheets? What I have tried so far: (it fails with a message telling me that I cannot have duplicate names; if I exit the routine after the Worksheets(WBSsht).Delete line, then the sheet is still present under MS Objects. Public WBSsht As Object Option Explicit Sub procMain() Dim sh As Worksheet Dim rngPlaceHolder As Range 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") Set sh = WBSsht If sh Is Nothing Then 'Doesn't exist Set sh = Nothing On Error GoTo 0 Else 'Does exist - delete it before proceeding Set sh = Nothing 'Application.DisplayAlerts = False Worksheets(WBSsht).Delete 'Application.DisplayAlerts = True On Error GoTo 0 End If Set WBSsht = Worksheets.Add 'and give it a name WBSsht.Name = ("WBSlist") Worksheets("WBSlist").Visible = xlVeryHidden End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting veryHidden worksheets
I tried some more, and was unable to replicate the crash. This worked through
3 iterations (Excel 2003). I walked through it line by line (F8) switching back to the workbook at each step to verify that the hidden sheet never showed: Sub testSheetDelete() Sheet3.Visible = xlSheetVeryHidden Application.ScreenUpdating = False Application.DisplayAlerts = False Sheet3.Visible = xlSheetHidden Sheet3.Delete End Sub HTH, Keith "ker_01" wrote: Interesting problem; I tried to come up with a solution (unsuccessfully) but maybe these will give you additional ideas. In Excel 2003 I tried screenupdating=false, but the sheet still appeared when I set it to visible before deleting it. I also tried changing the visibility from veryhidden to just hidden, then deleting the sheet, and it crashed Excel 2003. On recovering the document, I see that sheet is visible in the VBA project pane, but is no longer a worksheet (it has the same icon as "ThisWorkbook" instead of the other sheets). Is it critical that the sheet not be seen at all (not even the sheet tab) or do you just need to hide the sheet contents? I'd think that as long as another sheet is active, you could unhide your target sheet and delete it, and the only visual indication would be the (very brief) appearance of a worksheet tab. Sorry I don't have a complete solution, Keith "Project Mangler" wrote: Can a worksheet which is very hidden be deleted without being made visible first? I can manipulate a veryhidden sheet using an object variable, but I either can't get the syntax right to delete it or there is some other problem beyond my limited knowledge. Can someone please shed some light on how to delete such sheets? What I have tried so far: (it fails with a message telling me that I cannot have duplicate names; if I exit the routine after the Worksheets(WBSsht).Delete line, then the sheet is still present under MS Objects. Public WBSsht As Object Option Explicit Sub procMain() Dim sh As Worksheet Dim rngPlaceHolder As Range 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") Set sh = WBSsht If sh Is Nothing Then 'Doesn't exist Set sh = Nothing On Error GoTo 0 Else 'Does exist - delete it before proceeding Set sh = Nothing 'Application.DisplayAlerts = False Worksheets(WBSsht).Delete 'Application.DisplayAlerts = True On Error GoTo 0 End If Set WBSsht = Worksheets.Add 'and give it a name WBSsht.Name = ("WBSlist") Worksheets("WBSlist").Visible = xlVeryHidden End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting veryHidden worksheets
"Worksheets(WBSsht).Delete" is incorrect syntax - a string is required for the worksheet name. This works for me... '--- Public WBSsht As Object Sub procMain() 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") On Error GoTo 0 Application.ScreenUpdating = False If WBSsht Is Nothing Then 'Doesn't exist Else 'Does exist - delete it before proceeding WBSsht.Visible = True Application.DisplayAlerts = False If Sheets.Count 1 Then WBSsht.Delete Else Worksheets.Add Count:=1 WBSsht.Delete End If Application.DisplayAlerts = True End If Set WBSsht = Worksheets.Add(Count:=1) WBSsht.Name = "WBSlist" Worksheets("WBSlist").Visible = xlVeryHidden Application.ScreenUpdating = True Set WBSsht = Nothing '? End Sub -- Jim Cone Portland, Oregon USA free Excel programs... http://excelusergroup.org/media/ "Project Mangler" wrote in message ... Can a worksheet which is very hidden be deleted without being made visible first? I can manipulate a veryhidden sheet using an object variable, but I either can't get the syntax right to delete it or there is some other problem beyond my limited knowledge. Can someone please shed some light on how to delete such sheets? What I have tried so far: (it fails with a message telling me that I cannot have duplicate names; if I exit the routine after the Worksheets(WBSsht).Delete line, then the sheet is still present under MS Objects. Public WBSsht As Object Option Explicit Sub procMain() Dim sh As Worksheet Dim rngPlaceHolder As Range 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") Set sh = WBSsht If sh Is Nothing Then 'Doesn't exist Set sh = Nothing On Error GoTo 0 Else 'Does exist - delete it before proceeding Set sh = Nothing 'Application.DisplayAlerts = False Worksheets(WBSsht).Delete 'Application.DisplayAlerts = True On Error GoTo 0 End If Set WBSsht = Worksheets.Add 'and give it a name WBSsht.Name = ("WBSlist") Worksheets("WBSlist").Visible = xlVeryHidden End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting veryHidden worksheets
Morning ker_01
Thanks for taking a look at this. I didn't get any crashes, but the sheet seemed impervious to deletion unless I made it visible first. It isn't critical that it doesn't become visible, I'm just looking to improve my understanding and use the "correct" or "best" method if one exists rather than finding a work-around. I've combined Jim's code and your suggestion of making it just hidden into a working solution. Thanks again. DB "ker_01" wrote in message ... I tried some more, and was unable to replicate the crash. This worked through 3 iterations (Excel 2003). I walked through it line by line (F8) switching back to the workbook at each step to verify that the hidden sheet never showed: Sub testSheetDelete() Sheet3.Visible = xlSheetVeryHidden Application.ScreenUpdating = False Application.DisplayAlerts = False Sheet3.Visible = xlSheetHidden Sheet3.Delete End Sub HTH, Keith "ker_01" wrote: Interesting problem; I tried to come up with a solution (unsuccessfully) but maybe these will give you additional ideas. In Excel 2003 I tried screenupdating=false, but the sheet still appeared when I set it to visible before deleting it. I also tried changing the visibility from veryhidden to just hidden, then deleting the sheet, and it crashed Excel 2003. On recovering the document, I see that sheet is visible in the VBA project pane, but is no longer a worksheet (it has the same icon as "ThisWorkbook" instead of the other sheets). Is it critical that the sheet not be seen at all (not even the sheet tab) or do you just need to hide the sheet contents? I'd think that as long as another sheet is active, you could unhide your target sheet and delete it, and the only visual indication would be the (very brief) appearance of a worksheet tab. Sorry I don't have a complete solution, Keith "Project Mangler" wrote: Can a worksheet which is very hidden be deleted without being made visible first? I can manipulate a veryhidden sheet using an object variable, but I either can't get the syntax right to delete it or there is some other problem beyond my limited knowledge. Can someone please shed some light on how to delete such sheets? What I have tried so far: (it fails with a message telling me that I cannot have duplicate names; if I exit the routine after the Worksheets(WBSsht).Delete line, then the sheet is still present under MS Objects. Public WBSsht As Object Option Explicit Sub procMain() Dim sh As Worksheet Dim rngPlaceHolder As Range 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") Set sh = WBSsht If sh Is Nothing Then 'Doesn't exist Set sh = Nothing On Error GoTo 0 Else 'Does exist - delete it before proceeding Set sh = Nothing 'Application.DisplayAlerts = False Worksheets(WBSsht).Delete 'Application.DisplayAlerts = True On Error GoTo 0 End If Set WBSsht = Worksheets.Add 'and give it a name WBSsht.Name = ("WBSlist") Worksheets("WBSlist").Visible = xlVeryHidden End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting veryHidden worksheets
Jim,
Thanks for the code below. I've had a look to see where I was going wrong and amended my code accordingly (and tried to understand it). Using ker_01's suggestion of making the sheet hidden before deletion is also good for me. Thanks for your help! DB "Jim Cone" wrote in message ... "Worksheets(WBSsht).Delete" is incorrect syntax - a string is required for the worksheet name. This works for me... '--- Public WBSsht As Object Sub procMain() 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") On Error GoTo 0 Application.ScreenUpdating = False If WBSsht Is Nothing Then 'Doesn't exist Else 'Does exist - delete it before proceeding WBSsht.Visible = True Application.DisplayAlerts = False If Sheets.Count 1 Then WBSsht.Delete Else Worksheets.Add Count:=1 WBSsht.Delete End If Application.DisplayAlerts = True End If Set WBSsht = Worksheets.Add(Count:=1) WBSsht.Name = "WBSlist" Worksheets("WBSlist").Visible = xlVeryHidden Application.ScreenUpdating = True Set WBSsht = Nothing '? End Sub -- Jim Cone Portland, Oregon USA free Excel programs... http://excelusergroup.org/media/ "Project Mangler" wrote in message ... Can a worksheet which is very hidden be deleted without being made visible first? I can manipulate a veryhidden sheet using an object variable, but I either can't get the syntax right to delete it or there is some other problem beyond my limited knowledge. Can someone please shed some light on how to delete such sheets? What I have tried so far: (it fails with a message telling me that I cannot have duplicate names; if I exit the routine after the Worksheets(WBSsht).Delete line, then the sheet is still present under MS Objects. Public WBSsht As Object Option Explicit Sub procMain() Dim sh As Worksheet Dim rngPlaceHolder As Range 'Add a blank worksheet but check it exists first On Error Resume Next Set WBSsht = Worksheets("WBSlist") Set sh = WBSsht If sh Is Nothing Then 'Doesn't exist Set sh = Nothing On Error GoTo 0 Else 'Does exist - delete it before proceeding Set sh = Nothing 'Application.DisplayAlerts = False Worksheets(WBSsht).Delete 'Application.DisplayAlerts = True On Error GoTo 0 End If Set WBSsht = Worksheets.Add 'and give it a name WBSsht.Name = ("WBSlist") Worksheets("WBSlist").Visible = xlVeryHidden End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help me find veryhidden sheets | Excel Programming | |||
VeryHidden causing a problem...? | Excel Programming | |||
VeryHidden | Excel Programming | |||
Sheet hidden and VeryHidden | Excel Discussion (Misc queries) | |||
Problems with vb.veryhidden | Excel Programming |