Error trap the renaming of a sheet to an existing one
 

Hi,
I am using the following code to insert and rename a new worksheet with a
user input variable

Sheets.Add.Name = sname

This works fine until the user trys to insert a sheet of the same name as an
existing one.

I can trap error 1004 but how can I delete the sheet that has just been
inserted? I notice that when I insert a worksheet it always increments the
sheet no. by 1 regardless of whether the previous worksheets exist eg I have
just inserted a worksheet into my workbook that has only 1 other remaining
worksheet, the others having been deleted, but it still gave it the default
name of Sheet16 - Does access store the number of worksheets somewhere so
that I can delete the max one?

If not how would you suggest I go about deleting the latest inserted
worksheet?

Thanks

Error trap the renaming of a sheet to an existing one
 

Newbie,

I would use the fact that the sheet just added becomes the activesheet, so
you can say "Activesheet.Delete." This is what I came up with:

On Error Resume Next
Sheets.Add.Name = sname
If Err = 1004 Then
Application.DisplayAlerts = False 'So it won't prompt to confirm
deletion
ActiveSheet.Delete
Application.DisplayAlerts = True
MsgBox "That sheet name is already being used"
End If
On Error GoTo 0

hth,

Doug

"Newbie" wrote in message
...
Hi,
I am using the following code to insert and rename a new worksheet with a
user input variable

Sheets.Add.Name = sname

This works fine until the user trys to insert a sheet of the same name as
an
existing one.

I can trap error 1004 but how can I delete the sheet that has just been
inserted? I notice that when I insert a worksheet it always increments the
sheet no. by 1 regardless of whether the previous worksheets exist eg I
have
just inserted a worksheet into my workbook that has only 1 other remaining
worksheet, the others having been deleted, but it still gave it the
default
name of Sheet16 - Does access store the number of worksheets somewhere so
that I can delete the max one?

If not how would you suggest I go about deleting the latest inserted
worksheet?

Thanks

Error trap the renaming of a sheet to an existing one
 

How about trapping before the error occurs:

Dim s
Dim bFound as Boolean

bFound = False
For Each s in Sheets
If s.Name = sname Then bFound = True
Next

If bFound = True Then
Msgbox "Sheet name already entered", vbInformation
Exit Sub
End If

Sheets.Add.Name = sname



--
Message posted from http://www.ExcelForum.com

Error trap the renaming of a sheet to an existing one
 

Hi Newbie,

Here's a quick function that will tell you if a sheet exists or not:

Public Function gbSheetExists(rsName As String, _
Optional rwb As Workbook = Nothing) As Boolean
On Error Resume Next
If rwb Is Nothing Then Set rwb = ActiveWorkbook
gbSheetExists = Len(rwb.Sheets(rsName).Name)
On Error GoTo 0
End Function

In your case, you could keep asking the user for a sheet name until
gbSheetExists(sName) is False.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Newbie wrote:
Hi,
I am using the following code to insert and rename a new worksheet
with a user input variable

Sheets.Add.Name = sname

This works fine until the user trys to insert a sheet of the same
name as an existing one.

I can trap error 1004 but how can I delete the sheet that has just
been inserted? I notice that when I insert a worksheet it always
increments the sheet no. by 1 regardless of whether the previous
worksheets exist eg I have just inserted a worksheet into my workbook
that has only 1 other remaining worksheet, the others having been
deleted, but it still gave it the default name of Sheet16 - Does
access store the number of worksheets somewhere so that I can delete
the max one?

If not how would you suggest I go about deleting the latest inserted
worksheet?

Thanks

Error trap the renaming of a sheet to an existing one
 

Thanks - trapping before the error I agree is a better way to go

Thanks again
A
"kkknie " wrote in message
...
How about trapping before the error occurs:

Dim s
Dim bFound as Boolean

bFound = False
For Each s in Sheets
If s.Name = sname Then bFound = True
Next

If bFound = True Then
Msgbox "Sheet name already entered", vbInformation
Exit Sub
End If

Sheets.Add.Name = sname

K


---
Message posted from http://www.ExcelForum.com/