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