Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error.Type or IsError to trap #VALUE! and #NUM! | Excel Worksheet Functions | |||
Error Trap Not Working | Excel Programming | |||
Trap a DateValue Error | Excel Programming | |||
error trap | Excel Programming |