difference between 2000 and 2002?
 

Under Excel 2002 the following works just fine:

Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With

A new sheet named "scratch" is created and hidden. However, under Excel 2000
no new sheet is created yet no error occurs. In fact no sheet by any name
whatsoever is added under 2000. The thing doesn't complain but it doesn't
add a sheet either.

Here is the entire subroutine just in case something is wrong with the
events leading up to the malfeasant section...

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub

The part that deletes any existing "scratch" sheet works just fine in 2000
or 2002. If a "scratch" sheet exists it is indeed deleted. It's just that
with 2000 no new sheet ever gets added. This situation is discovered a bit
later when another snippet of code tries the following:

'hider = xlVeryHidden
hider = False
Sheets("data").Visible = hider
Sheets("scratch").Visible = hider
Sheets("formats").Visible = hider

The 'Sheets("scratch").Visible = hider' line dies with the predictable
"Run-time error 9: Subscript out of range" error since there is no
Sheets("scratch")

I really need to fix this. What I really don't need is a "Doc it hurts when
I do this", "Then don't do that" answer. This, right here, HAS to work.

Anyone have any insight here?

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

difference between 2000 and 2002?
 

Terry,

I suspect it has to do with visibility..

Remember ANY workbook(visible/hidden or addin) needs at least 1 visible
sheet.

So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..

Note this proc DOES test for visibility of the window. It DOESNOT test
for visibility on the sheets before deleting or hiding scratch. So be
carefull to leave 1 sheet visible at all times.

Note an addin will not appear in workbooks collection


Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object

If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If

With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With

If Not sh Is Nothing Then sh.Activate

End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry von Gease" wrote:

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub

difference between 2000 and 2002?
 

Thanks for the reply and the work, but most likely that isn't the probelm.

Here's some additional information:

It's not an addin, it's an xls file.

There's ALWAYS at least two other sheets visible at all times.

Scratch is ALWAYS hidden except at that moment when it's created via the add
and the subsequent instruction to set it's visibility to False has yet to
execute. Moreover, the delete of scratch works just fine, it's the add that
doesn't work. Worse it doesn't tell anyone that it didn't work, giving the
illusion that all is well.

There is NO error generated, the Worksheets.Add gives no indication that it
failed to function, yet it fails to function.

The code works flawlessly on Excel 2002, it does not function properly on
Excel 2000. That's my big problem here.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^

For a little history, the reason that I have to delete and then add the
sheet is because this sheet is prone to corruption. If merely cleared, all
cells deleted, or what-have-you it ends up that inexplicably, from time to
time, the cell A1 refuses to contain anything. That It has nothing to do
with formats, macros, or any other thing, the cell simply will not contain
anything. No matter what the state of the sheet or the cell or how you try
to get something into the cell. It refuses to contain anything. I cast this
bread upon both these and Microsoft's waters and no cogent explanation for
this phenomena or how to make it go away was forthcoming. Hence the crude
and hideously inelegant delete and add.

I wish I could get rid of this entire procedure, it's existence offends me.
But I can't seem to keep things working on the scratch sheet without doing
the delete and add. That being the case, I really need to get this working
on 2000 ASAP.

I'll entertain any reasonable alternative.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"keepitcool" wrote in message
...
Terry,

I suspect it has to do with visibility..

Remember ANY workbook(visible/hidden or addin) needs at least 1 visible
sheet.

So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..

Note this proc DOES test for visibility of the window. It DOESNOT test
for visibility on the sheets before deleting or hiding scratch. So be
carefull to leave 1 sheet visible at all times.

Note an addin will not appear in workbooks collection


Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object

If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If

With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With

If Not sh Is Nothing Then sh.Activate

End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry von Gease" wrote:

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub

difference between 2000 and 2002?
 

the code worked flawlessly for me in Excel 2000 and Excel 97.

Try it in a new workbook.

I guess you will have to figure out what is different.

Sure you don't have On Error Resume Next set somewhere above this so any
error is suppressed?

--
Regards,
Tom Ogilvy

Terry von Gease wrote in message
...
Thanks for the reply and the work, but most likely that isn't the probelm.

Here's some additional information:

It's not an addin, it's an xls file.

There's ALWAYS at least two other sheets visible at all times.

Scratch is ALWAYS hidden except at that moment when it's created via the
add
and the subsequent instruction to set it's visibility to False has yet to
execute. Moreover, the delete of scratch works just fine, it's the add
that
doesn't work. Worse it doesn't tell anyone that it didn't work, giving the
illusion that all is well.

There is NO error generated, the Worksheets.Add gives no indication that
it
failed to function, yet it fails to function.

The code works flawlessly on Excel 2002, it does not function properly on
Excel 2000. That's my big problem here.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^

For a little history, the reason that I have to delete and then add the
sheet is because this sheet is prone to corruption. If merely cleared, all
cells deleted, or what-have-you it ends up that inexplicably, from time to
time, the cell A1 refuses to contain anything. That It has nothing to do
with formats, macros, or any other thing, the cell simply will not contain
anything. No matter what the state of the sheet or the cell or how you try
to get something into the cell. It refuses to contain anything. I cast
this
bread upon both these and Microsoft's waters and no cogent explanation for
this phenomena or how to make it go away was forthcoming. Hence the crude
and hideously inelegant delete and add.

I wish I could get rid of this entire procedure, it's existence offends
me.
But I can't seem to keep things working on the scratch sheet without doing
the delete and add. That being the case, I really need to get this working
on 2000 ASAP.

I'll entertain any reasonable alternative.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"keepitcool" wrote in message
...
Terry,

I suspect it has to do with visibility..

Remember ANY workbook(visible/hidden or addin) needs at least 1 visible
sheet.

So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..

Note this proc DOES test for visibility of the window. It DOESNOT test
for visibility on the sheets before deleting or hiding scratch. So be
carefull to leave 1 sheet visible at all times.

Note an addin will not appear in workbooks collection


Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object

If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If

With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With

If Not sh Is Nothing Then sh.Activate

End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry von Gease" wrote:

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub

difference between 2000 and 2002?
 

Your code works fine on my Excel 2000. I have used this also numerous times
with success:

Sheets.Add
ActiveSheet.Name = "Scratch"

I also delete/add a helper sheet just to make sure I am using a 'clean'
sheet every time I open the WB. I only delete the sheet upon WB close. I use
this code in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' This sub deletes the temporary Sheet where the data
' was manipulated, when the wb is closed.
Application.DisplayAlerts = False
On Error GoTo 1
If Me.Saved = True Then
Sheets("Scratch").Select
ActiveWindow.SelectedSheets.Delete
Else
Sheets("Scratch").Select
ActiveWindow.SelectedSheets.Delete
End If
1:
Application.DisplayAlerts = True
End Sub

I hope this helps,
Mike

"Terry von Gease" wrote in message
...
Thanks for the reply and the work, but most likely that isn't the probelm.

Here's some additional information:

It's not an addin, it's an xls file.

There's ALWAYS at least two other sheets visible at all times.

Scratch is ALWAYS hidden except at that moment when it's created via the
add
and the subsequent instruction to set it's visibility to False has yet to
execute. Moreover, the delete of scratch works just fine, it's the add
that
doesn't work. Worse it doesn't tell anyone that it didn't work, giving the
illusion that all is well.

There is NO error generated, the Worksheets.Add gives no indication that
it
failed to function, yet it fails to function.

The code works flawlessly on Excel 2002, it does not function properly on
Excel 2000. That's my big problem here.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^

For a little history, the reason that I have to delete and then add the
sheet is because this sheet is prone to corruption. If merely cleared, all
cells deleted, or what-have-you it ends up that inexplicably, from time to
time, the cell A1 refuses to contain anything. That It has nothing to do
with formats, macros, or any other thing, the cell simply will not contain
anything. No matter what the state of the sheet or the cell or how you try
to get something into the cell. It refuses to contain anything. I cast
this
bread upon both these and Microsoft's waters and no cogent explanation for
this phenomena or how to make it go away was forthcoming. Hence the crude
and hideously inelegant delete and add.

I wish I could get rid of this entire procedure, it's existence offends
me.
But I can't seem to keep things working on the scratch sheet without doing
the delete and add. That being the case, I really need to get this working
on 2000 ASAP.

I'll entertain any reasonable alternative.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"keepitcool" wrote in message
...
Terry,

I suspect it has to do with visibility..

Remember ANY workbook(visible/hidden or addin) needs at least 1 visible
sheet.

So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..

Note this proc DOES test for visibility of the window. It DOESNOT test
for visibility on the sheets before deleting or hiding scratch. So be
carefull to leave 1 sheet visible at all times.

Note an addin will not appear in workbooks collection


Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object

If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If

With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With

If Not sh Is Nothing Then sh.Activate

End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry von Gease" wrote:

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub

difference between 2000 and 2002?
 

Not that simple.

The malfeasant code is but a drop in a bucket of well over 2Mb of VBA, plus
actual worksheets, that was created on a machine running XP Pro and Office
Pro 2002. The entire thing consists of three xls files, one big bugger with
all the code, one for a persistent list of names, and one with worksheets
with which the user interacts. The supporting files eventually will evolve
into xla files, but not at this time.

It was zipped up and sent off to a confederate running Windows 2000 and
Excel 2000. There it exhibits this strange behavior.

Moreover it did EXACTLY the same thing when it was tried on another machine
running Excel 2000 on Windows 98 as well as windows 2000 on that machine. In
this case when the Excel was upgraded to 2002 the problem went away.

I full well realize that there's nothing wrong with the code, It's something
about the version mix that would appear to be the culprit. Nonetheless, it
has to run on both 2000 and 2002.

Perhaps it needs to be opened with macros disabled and then compiled and
saved on the machine running Excel 2000. Would this force Excel to
reconsider the code in any way different than just using it? Is there any
way to convince my Excel 2002 that it's really Excel 2000 If so, would it do
any good? Doing a 'save as' into one of the myriad of other formats
provided?

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"Tom Ogilvy" wrote in message
...
the code worked flawlessly for me in Excel 2000 and Excel 97.

Try it in a new workbook.

I guess you will have to figure out what is different.

Sure you don't have On Error Resume Next set somewhere above this so any
error is suppressed?

--
Regards,
Tom Ogilvy

Terry von Gease wrote in message
...
Thanks for the reply and the work, but most likely that isn't the
probelm.

Here's some additional information:

It's not an addin, it's an xls file.

There's ALWAYS at least two other sheets visible at all times.

Scratch is ALWAYS hidden except at that moment when it's created via the
add
and the subsequent instruction to set it's visibility to False has yet
to
execute. Moreover, the delete of scratch works just fine, it's the add
that
doesn't work. Worse it doesn't tell anyone that it didn't work, giving
the
illusion that all is well.

There is NO error generated, the Worksheets.Add gives no indication that
it
failed to function, yet it fails to function.

The code works flawlessly on Excel 2002, it does not function properly
on
Excel 2000. That's my big problem here.


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^

For a little history, the reason that I have to delete and then add the
sheet is because this sheet is prone to corruption. If merely cleared,
all
cells deleted, or what-have-you it ends up that inexplicably, from time
to
time, the cell A1 refuses to contain anything. That It has nothing to do
with formats, macros, or any other thing, the cell simply will not
contain
anything. No matter what the state of the sheet or the cell or how you
try
to get something into the cell. It refuses to contain anything. I cast
this
bread upon both these and Microsoft's waters and no cogent explanation
for
this phenomena or how to make it go away was forthcoming. Hence the
crude
and hideously inelegant delete and add.

I wish I could get rid of this entire procedure, it's existence offends
me.
But I can't seem to keep things working on the scratch sheet without
doing
the delete and add. That being the case, I really need to get this
working
on 2000 ASAP.

I'll entertain any reasonable alternative.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"keepitcool" wrote in message
...
Terry,

I suspect it has to do with visibility..

Remember ANY workbook(visible/hidden or addin) needs at least 1
visible
sheet.

So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..

Note this proc DOES test for visibility of the window. It DOESNOT test
for visibility on the sheets before deleting or hiding scratch. So be
carefull to leave 1 sheet visible at all times.

Note an addin will not appear in workbooks collection


Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object

If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If

With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With

If Not sh Is Nothing Then sh.Activate

End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry von Gease" wrote:

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub

difference between 2000 and 2002?
 

There is only one format for xl97 through xl2003. Doing a file save as will
do nothing to fix code. If you do a save as to a version like xl5/xl95, you
will lose any useforms for sure.

As suggested many times, development should be undertaken using the lowest
version of excel for which the code would be used.

Your symptoms point to error handling causing you to miss the error - the
error being caused by an attribute recognized in xl2002 but not in xl2000.
How that figures in this code I can't say. Compiling in xl2000 should
reveal the error if that is the cause.

--
Regards,
Tom Ogilvy


Terry von Gease wrote in message
...
Not that simple.

The malfeasant code is but a drop in a bucket of well over 2Mb of VBA,
plus
actual worksheets, that was created on a machine running XP Pro and Office
Pro 2002. The entire thing consists of three xls files, one big bugger
with
all the code, one for a persistent list of names, and one with worksheets
with which the user interacts. The supporting files eventually will evolve
into xla files, but not at this time.

It was zipped up and sent off to a confederate running Windows 2000 and
Excel 2000. There it exhibits this strange behavior.

Moreover it did EXACTLY the same thing when it was tried on another
machine
running Excel 2000 on Windows 98 as well as windows 2000 on that machine.
In
this case when the Excel was upgraded to 2002 the problem went away.

I full well realize that there's nothing wrong with the code, It's
something
about the version mix that would appear to be the culprit. Nonetheless, it
has to run on both 2000 and 2002.

Perhaps it needs to be opened with macros disabled and then compiled and
saved on the machine running Excel 2000. Would this force Excel to
reconsider the code in any way different than just using it? Is there any
way to convince my Excel 2002 that it's really Excel 2000 If so, would it
do
any good? Doing a 'save as' into one of the myriad of other formats
provided?

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"Tom Ogilvy" wrote in message
...
the code worked flawlessly for me in Excel 2000 and Excel 97.

Try it in a new workbook.

I guess you will have to figure out what is different.

Sure you don't have On Error Resume Next set somewhere above this so any
error is suppressed?

--
Regards,
Tom Ogilvy

Terry von Gease wrote in message
...
Thanks for the reply and the work, but most likely that isn't the
probelm.

Here's some additional information:

It's not an addin, it's an xls file.

There's ALWAYS at least two other sheets visible at all times.

Scratch is ALWAYS hidden except at that moment when it's created via
the
add
and the subsequent instruction to set it's visibility to False has yet
to
execute. Moreover, the delete of scratch works just fine, it's the add
that
doesn't work. Worse it doesn't tell anyone that it didn't work, giving
the
illusion that all is well.

There is NO error generated, the Worksheets.Add gives no indication
that
it
failed to function, yet it fails to function.

The code works flawlessly on Excel 2002, it does not function properly
on
Excel 2000. That's my big problem here.



^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^

For a little history, the reason that I have to delete and then add
the
sheet is because this sheet is prone to corruption. If merely cleared,
all
cells deleted, or what-have-you it ends up that inexplicably, from
time
to
time, the cell A1 refuses to contain anything. That It has nothing to
do
with formats, macros, or any other thing, the cell simply will not
contain
anything. No matter what the state of the sheet or the cell or how you
try
to get something into the cell. It refuses to contain anything. I cast
this
bread upon both these and Microsoft's waters and no cogent explanation
for
this phenomena or how to make it go away was forthcoming. Hence the
crude
and hideously inelegant delete and add.

I wish I could get rid of this entire procedure, it's existence
offends
me.
But I can't seem to keep things working on the scratch sheet without
doing
the delete and add. That being the case, I really need to get this
working
on 2000 ASAP.

I'll entertain any reasonable alternative.

--
Terry

"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley

"keepitcool" wrote in message
...
Terry,

I suspect it has to do with visibility..

Remember ANY workbook(visible/hidden or addin) needs at least 1
visible
sheet.

So:
you cannot hide all sheets
you cannot delete scratch if it's the only visible sheet..

Note this proc DOES test for visibility of the window. It DOESNOT
test
for visibility on the sheets before deleting or hiding scratch. So
be
carefull to leave 1 sheet visible at all times.

Note an addin will not appear in workbooks collection


Sub new_scratch2()
Dim wb As Workbook
Dim sh As Object

If ActiveSheet Is Nothing Then
If Workbooks.Count = 0 Then
MsgBox "No open workbooks. Aborting."
Exit Sub
ElseIf MsgBox("Use hidden " & Workbooks(1).Name & _
"?", vbYesNo) = vbNo Then
Exit Sub
End If
Set wb = Workbooks(1)
Else
Set wb = ActiveWorkbook
Set sh = ActiveSheet
End If

With wb
Application.DisplayAlerts = False
On Error Resume Next
.Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
With .Worksheets.Add
.Name = "scratch"
.Visible = xlHidden
End With
End With

If Not sh Is Nothing Then sh.Activate

End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Terry von Gease" wrote:

Sub new_scratch()

Set sht = ActiveSheet
Application.DisplayAlerts = False
On Error Resume Next
Sheets("scratch").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add
With ActiveSheet
.Name = "scratch"
.Visible = False
End With
sht.Activate

End Sub