I received a -21474147848 error code (descr: method of 'Delete' of object
'_Worksheet' failed) when deleting a worksheet (using vba). The vba code is
held in a .xla file. This error issue was only occurring when I used a
particular workbook. I have no issues performing the same actions in a
different .xls workbook.

I don't know if I solved the issue or just delayed it, but this is what I
did...

I modified the .xla code to 1. set the workbook calculation status to
Manual, 2. performed the delete worksheet code and then 3. returned the
workbook calcuation to Automatic.

Comments?

Thx
MSweetG222

Are you sure that you got the error message number right? The value you
provide causes an overflow on a Long data type, and system error messages
are always Long data types.

You can normally retrieve the exact error text associated with a system
error number using the GetSystemErrorMessageText function described on
http://www.cpearson.com/excel/FormatMessage.htm

This procedure, however, requires a Long data type for the error number, and
your error number is not a Long.

All that said, it sounds to me like you have the structure of the workbook
protected. Go to the Tools menu, choose Protection, and select UnProtect
Workbook. Supply the appropriate password if prompted.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)


"MSweetG222" <(E-Mail Removed)> wrote in message
newsF4175AF-2C8A-4118-BD4F-(E-Mail Removed)...
>I received a -21474147848 error code (descr: method of 'Delete' of object
> '_Worksheet' failed) when deleting a worksheet (using vba). The vba code
> is
> held in a .xla file. This error issue was only occurring when I used a
> particular workbook. I have no issues performing the same actions in a
> different .xls workbook.
>
> I don't know if I solved the issue or just delayed it, but this is what I
> did...
>
> I modified the .xla code to 1. set the workbook calculation status to
> Manual, 2. performed the delete worksheet code and then 3. returned the
> workbook calcuation to Automatic.
>
> Comments?
>
> Thx
> MSweetG222
>

Chip - here is the error ... -2147417848 Automation error (I cut and pasted
from the immediate window, ie: err.number & err.description)

Here is what I receive when I run your procedure...
"The object invoked has disconnected from its clients. "

The line of code that it was received on ...
Sheets(sWorksheet).Delete
where sWorksheet is a sheet name

Is that information helpful?

Thx
MSweetG222



"Chip Pearson" wrote:

> Are you sure that you got the error message number right? The value you
> provide causes an overflow on a Long data type, and system error messages
> are always Long data types.
>
> You can normally retrieve the exact error text associated with a system
> error number using the GetSystemErrorMessageText function described on
> http://www.cpearson.com/excel/FormatMessage.htm
>
> This procedure, however, requires a Long data type for the error number, and
> your error number is not a Long.
>
> All that said, it sounds to me like you have the structure of the workbook
> protected. Go to the Tools menu, choose Protection, and select UnProtect
> Workbook. Supply the appropriate password if prompted.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on the web site)
>
>
> "MSweetG222" <(E-Mail Removed)> wrote in message
> newsF4175AF-2C8A-4118-BD4F-(E-Mail Removed)...
> >I received a -21474147848 error code (descr: method of 'Delete' of object
> > '_Worksheet' failed) when deleting a worksheet (using vba). The vba code
> > is
> > held in a .xla file. This error issue was only occurring when I used a
> > particular workbook. I have no issues performing the same actions in a
> > different .xls workbook.
> >
> > I don't know if I solved the issue or just delayed it, but this is what I
> > did...
> >
> > I modified the .xla code to 1. set the workbook calculation status to
> > Manual, 2. performed the delete worksheet code and then 3. returned the
> > workbook calcuation to Automatic.
> >
> > Comments?
> >
> > Thx
> > MSweetG222
> >
>
>
>

You usually receive an "Object Disconnected" error when you attempt to use a
variable that refers to an object that has been deleted. See
http://www.cpearson.com/excel/ConnectedObject.htm for more information about
disconnected variables.

For example, you can get a disconnect error with

Dim WS As Worksheet
Set WS = Worksheets(2)
Application.DisplayAlerts = False
Worksheets(2).Delete
Application.DisplayAlerts = True
Debug.Print WS.Name

Here, WS no longer refers to an existing worksheet (it got deleted) so
you'll a disconnect error (WS does not automatically get set to Nothing)..

But this doesn't seem to make sense in the code you posted. Is sWorksheet a
String variable? Does the sheet named in the sWorksheet variable exist in
the ActiveWorkbook (which may not be the same as the workbook containing the
code)? Is the workbook protected?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)



"MSweetG222" <(E-Mail Removed)> wrote in message
news:8C8B19FC-7A0D-4796-BECF-(E-Mail Removed)...
> Chip - here is the error ... -2147417848 Automation error (I cut and
> pasted
> from the immediate window, ie: err.number & err.description)
>
> Here is what I receive when I run your procedure...
> "The object invoked has disconnected from its clients. "
>
> The line of code that it was received on ...
> Sheets(sWorksheet).Delete
> where sWorksheet is a sheet name
>
> Is that information helpful?
>
> Thx
> MSweetG222
>
>
>
> "Chip Pearson" wrote:
>
>> Are you sure that you got the error message number right? The value you
>> provide causes an overflow on a Long data type, and system error messages
>> are always Long data types.
>>
>> You can normally retrieve the exact error text associated with a system
>> error number using the GetSystemErrorMessageText function described on
>> http://www.cpearson.com/excel/FormatMessage.htm
>>
>> This procedure, however, requires a Long data type for the error number,
>> and
>> your error number is not a Long.
>>
>> All that said, it sounds to me like you have the structure of the
>> workbook
>> protected. Go to the Tools menu, choose Protection, and select UnProtect
>> Workbook. Supply the appropriate password if prompted.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>> (email on the web site)
>>
>>
>> "MSweetG222" <(E-Mail Removed)> wrote in message
>> newsF4175AF-2C8A-4118-BD4F-(E-Mail Removed)...
>> >I received a -21474147848 error code (descr: method of 'Delete' of
>> >object
>> > '_Worksheet' failed) when deleting a worksheet (using vba). The vba
>> > code
>> > is
>> > held in a .xla file. This error issue was only occurring when I used a
>> > particular workbook. I have no issues performing the same actions in
>> > a
>> > different .xls workbook.
>> >
>> > I don't know if I solved the issue or just delayed it, but this is what
>> > I
>> > did...
>> >
>> > I modified the .xla code to 1. set the workbook calculation status to
>> > Manual, 2. performed the delete worksheet code and then 3. returned the
>> > workbook calcuation to Automatic.
>> >
>> > Comments?
>> >
>> > Thx
>> > MSweetG222
>> >
>>
>>
>>

Chip, Thank you for your response.

sWorksheet is a string. The worksheet to be deleted is in another workbook
and for this particular workbook, the worksheet does not exist. (For other
workbooks, it does exist) and the workbook is not protected.

Is there a "test" I need to run to check to see if the worksheet exists
before I delete?

Again, thanks for any assistance you can give me.

Thx
MSweetG222



"Chip Pearson" wrote:

> You usually receive an "Object Disconnected" error when you attempt to use a
> variable that refers to an object that has been deleted. See
> http://www.cpearson.com/excel/ConnectedObject.htm for more information about
> disconnected variables.
>
> For example, you can get a disconnect error with
>
> Dim WS As Worksheet
> Set WS = Worksheets(2)
> Application.DisplayAlerts = False
> Worksheets(2).Delete
> Application.DisplayAlerts = True
> Debug.Print WS.Name
>
> Here, WS no longer refers to an existing worksheet (it got deleted) so
> you'll a disconnect error (WS does not automatically get set to Nothing)..
>
> But this doesn't seem to make sense in the code you posted. Is sWorksheet a
> String variable? Does the sheet named in the sWorksheet variable exist in
> the ActiveWorkbook (which may not be the same as the workbook containing the
> code)? Is the workbook protected?
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on the web site)
>
>
>
> "MSweetG222" <(E-Mail Removed)> wrote in message
> news:8C8B19FC-7A0D-4796-BECF-(E-Mail Removed)...
> > Chip - here is the error ... -2147417848 Automation error (I cut and
> > pasted
> > from the immediate window, ie: err.number & err.description)
> >
> > Here is what I receive when I run your procedure...
> > "The object invoked has disconnected from its clients. "
> >
> > The line of code that it was received on ...
> > Sheets(sWorksheet).Delete
> > where sWorksheet is a sheet name
> >
> > Is that information helpful?
> >
> > Thx
> > MSweetG222
> >
> >
> >
> > "Chip Pearson" wrote:
> >
> >> Are you sure that you got the error message number right? The value you
> >> provide causes an overflow on a Long data type, and system error messages
> >> are always Long data types.
> >>
> >> You can normally retrieve the exact error text associated with a system
> >> error number using the GetSystemErrorMessageText function described on
> >> http://www.cpearson.com/excel/FormatMessage.htm
> >>
> >> This procedure, however, requires a Long data type for the error number,
> >> and
> >> your error number is not a Long.
> >>
> >> All that said, it sounds to me like you have the structure of the
> >> workbook
> >> protected. Go to the Tools menu, choose Protection, and select UnProtect
> >> Workbook. Supply the appropriate password if prompted.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email on the web site)
> >>
> >>
> >> "MSweetG222" <(E-Mail Removed)> wrote in message
> >> newsF4175AF-2C8A-4118-BD4F-(E-Mail Removed)...
> >> >I received a -21474147848 error code (descr: method of 'Delete' of
> >> >object
> >> > '_Worksheet' failed) when deleting a worksheet (using vba). The vba
> >> > code
> >> > is
> >> > held in a .xla file. This error issue was only occurring when I used a
> >> > particular workbook. I have no issues performing the same actions in
> >> > a
> >> > different .xls workbook.
> >> >
> >> > I don't know if I solved the issue or just delayed it, but this is what
> >> > I
> >> > did...
> >> >
> >> > I modified the .xla code to 1. set the workbook calculation status to
> >> > Manual, 2. performed the delete worksheet code and then 3. returned the
> >> > workbook calcuation to Automatic.
> >> >
> >> > Comments?
> >> >
> >> > Thx
> >> > MSweetG222
> >> >
> >>
> >>
> >>
>
>
>

I stole this from Chip:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

MSweetG222 wrote:
>
> Chip, Thank you for your response.
>
> sWorksheet is a string. The worksheet to be deleted is in another workbook
> and for this particular workbook, the worksheet does not exist. (For other
> workbooks, it does exist) and the workbook is not protected.
>
> Is there a "test" I need to run to check to see if the worksheet exists
> before I delete?
>
> Again, thanks for any assistance you can give me.
>
> Thx
> MSweetG222
>
> "Chip Pearson" wrote:
>
> > You usually receive an "Object Disconnected" error when you attempt to use a
> > variable that refers to an object that has been deleted. See
> > http://www.cpearson.com/excel/ConnectedObject.htm for more information about
> > disconnected variables.
> >
> > For example, you can get a disconnect error with
> >
> > Dim WS As Worksheet
> > Set WS = Worksheets(2)
> > Application.DisplayAlerts = False
> > Worksheets(2).Delete
> > Application.DisplayAlerts = True
> > Debug.Print WS.Name
> >
> > Here, WS no longer refers to an existing worksheet (it got deleted) so
> > you'll a disconnect error (WS does not automatically get set to Nothing)..
> >
> > But this doesn't seem to make sense in the code you posted. Is sWorksheet a
> > String variable? Does the sheet named in the sWorksheet variable exist in
> > the ActiveWorkbook (which may not be the same as the workbook containing the
> > code)? Is the workbook protected?
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> > (email on the web site)
> >
> >
> >
> > "MSweetG222" <(E-Mail Removed)> wrote in message
> > news:8C8B19FC-7A0D-4796-BECF-(E-Mail Removed)...
> > > Chip - here is the error ... -2147417848 Automation error (I cut and
> > > pasted
> > > from the immediate window, ie: err.number & err.description)
> > >
> > > Here is what I receive when I run your procedure...
> > > "The object invoked has disconnected from its clients. "
> > >
> > > The line of code that it was received on ...
> > > Sheets(sWorksheet).Delete
> > > where sWorksheet is a sheet name
> > >
> > > Is that information helpful?
> > >
> > > Thx
> > > MSweetG222
> > >
> > >
> > >
> > > "Chip Pearson" wrote:
> > >
> > >> Are you sure that you got the error message number right? The value you
> > >> provide causes an overflow on a Long data type, and system error messages
> > >> are always Long data types.
> > >>
> > >> You can normally retrieve the exact error text associated with a system
> > >> error number using the GetSystemErrorMessageText function described on
> > >> http://www.cpearson.com/excel/FormatMessage.htm
> > >>
> > >> This procedure, however, requires a Long data type for the error number,
> > >> and
> > >> your error number is not a Long.
> > >>
> > >> All that said, it sounds to me like you have the structure of the
> > >> workbook
> > >> protected. Go to the Tools menu, choose Protection, and select UnProtect
> > >> Workbook. Supply the appropriate password if prompted.
> > >>
> > >>
> > >> --
> > >> Cordially,
> > >> Chip Pearson
> > >> Microsoft MVP - Excel
> > >> Pearson Software Consulting, LLC
> > >> www.cpearson.com
> > >> (email on the web site)
> > >>
> > >>
> > >> "MSweetG222" <(E-Mail Removed)> wrote in message
> > >> newsF4175AF-2C8A-4118-BD4F-(E-Mail Removed)...
> > >> >I received a -21474147848 error code (descr: method of 'Delete' of
> > >> >object
> > >> > '_Worksheet' failed) when deleting a worksheet (using vba). The vba
> > >> > code
> > >> > is
> > >> > held in a .xla file. This error issue was only occurring when I used a
> > >> > particular workbook. I have no issues performing the same actions in
> > >> > a
> > >> > different .xls workbook.
> > >> >
> > >> > I don't know if I solved the issue or just delayed it, but this is what
> > >> > I
> > >> > did...
> > >> >
> > >> > I modified the .xla code to 1. set the workbook calculation status to
> > >> > Manual, 2. performed the delete worksheet code and then 3. returned the
> > >> > workbook calcuation to Automatic.
> > >> >
> > >> > Comments?
> > >> >
> > >> > Thx
> > >> > MSweetG222
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

Thank you Chip & Dave for your help!!

MSweetG222



"MSweetG222" wrote:

> I received a -21474147848 error code (descr: method of 'Delete' of object
> '_Worksheet' failed) when deleting a worksheet (using vba). The vba code is
> held in a .xla file. This error issue was only occurring when I used a
> particular workbook. I have no issues performing the same actions in a
> different .xls workbook.
>
> I don't know if I solved the issue or just delayed it, but this is what I
> did...
>
> I modified the .xla code to 1. set the workbook calculation status to
> Manual, 2. performed the delete worksheet code and then 3. returned the
> workbook calcuation to Automatic.
>
> Comments?
>
> Thx
> MSweetG222
>