Preventing An Error

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,851
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,953
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,851
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Works like a charm! Thank you Mike.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,200
Messages
5,623,336
Members
415,968
Latest member
Chabal74

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