Preventing An Error

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code (only a portion presented here...):

Rich (BB code):
Sub clean_up_database()

    Dim norec As Long 'number of records
    Dim red_ref As Long
    Dim red_fun As Long
    Dim red_fac As Long
    Dim red_class As Long
    Dim class_c As Long
    Dim myVal As Variant
    Dim dv As Long
    
    Set wb_main = Workbooks("Sports15b.xlsm")
    Set ws_vh = wb_main.Worksheets("VAR_HOLD")
    Set ws_th = wb_main.Worksheets("TEMP_HOLD")
    Set ws_rd = wb_main.Worksheets("Rental_Data")
    Set ws_fac = wb_main.Worksheets("Facilities")
    Set ws_front = wb_main.Worksheets("DYNAMIC")
    Set fac1 = ws_fac.Range("A:H")
    Set class1 = ws_fac.Range("B:B")
    
    qfile = ws_vh.Range("B3")
    
    Workbooks.Open ("H:\PWS\Parks\Parks Operations\Sports\Sports15\DATA1\" & qfile)
        
    Set wb_data = Workbooks(qfile)
    
    With wb_data
        .Worksheets.Add(After:=.Worksheets(.Worksheets.Count)).Name = "STAFF"
        .Sheets("Sheet1").Name = "CORE"
        Set ws_core = wb_data.Worksheets("CORE")
        'Set ws_core = wb_data.Worksheets("CORE")
        Set ws_staff = wb_data.Worksheets("STAFF")
    End With
    ...

I know why I am getting an error ('Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by visual basic') with the line in red. It's because this procedure has already been run already and has already added these sheets previously. The user may have gotten to this procedure by accidentally pressing a wrong command button.

I am wondering what the best way is to catch this error before it happens? If the user gets to this point and tries to open this file having already completed these tasks, identify they shouldn't be here, and exit to the home page. (worksheet("Dynamic"))
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,961
This will alert you if there is already a sheet named "STAFF"
If the flag is True (i.e. if it already exists), you can either exit the sub, or rename the existing sheet or use a different name for the new sheet, depending on your pleasure.
Code:
Dim flag as boolean

Flag = False
On Error Resume Next
Flag = (LCase(ThisWorkbook.Sheets("Staff").Name) = "staff")
On Error goto 0

If Flag then 
    MsgBox "There is already a sheet named Staff"
End If
 
Last edited:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Works like a charm! Thank you Mike.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top