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

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
>
>
>
> .
>

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
> >
> >
> >
> > .
> >

"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" <(E-Mail Removed)>
wrote in message news:(E-Mail Removed)...
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

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" <(E-Mail Removed)> wrote in message
news:7B05F692-0BAE-4E39-BAD0-(E-Mail Removed)...
> 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
> > >
> > >
> > >
> > > .
> > >

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> "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" <(E-Mail Removed)>
> wrote in message news:(E-Mail Removed)...
> 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
>
>
>