P
Project Mangler
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
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