Prev: Resize query
Next: How to import the links with method="POST" within form?
From: Project Mangler on 10 Apr 2010 04:47
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" <james.coneXXX(a)comcast.netXXX> wrote in message
news:%23kpAGqE2KHA.3844(a)TK2MSFTNGP05.phx.gbl...
>
> "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" <dblack(a)ntlworld.com>
> wrote in message news:e61BLID2KHA.5820(a)TK2MSFTNGP06.phx.gbl...
> 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  |  Prev  | 
Pages: 1 2
Prev: Resize query
Next: How to import the links with method="POST" within form?