Results 1 to 7 of 7

Thread: VBA code that stops and alerts user of error
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code that stops and alerts user of error

    Hi Everyone,
    I'm working on a VBA code that refers to a range of cells on my "Summary" page and creates a copy of a hidden "template" tab for each name listed in this range. My question is how can I have the code stop and alert the user of a duplicate name whenever that occurs rather than cause an error and immediately jump to debug? Below is a copy of my code. Any assistance tweaking this code to prevent the error would be appreciated.

    Sub makeSheets()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Set sh1 = Sheets("Template")
    Set sh2 = Sheets("Summary")
    Application.ScreenUpdating = False
    ActiveWorkbook.Sheets("Template").Visible = True
    For Each c In sh2.Range("B28", sh2.Cells(Rows.Count, 2).End(xlUp))
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = c.Value
    Next
    ActiveWorkbook.Sheets("Template").Visible = False
    Sheets("Summary").Select
    Application.ScreenUpdating = True
    End Sub

  2. #2
    New Member
    Join Date
    Jul 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code that stops and alerts user of error

    I'm sure it's a simple IF statement but I'm not sure about how to write it.

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code that stops and alerts user of error

    looking into it

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code that stops and alerts user of error

    ok put this statement on line 2 just under your Dim Statement:

    Code:
    On Error GoTo ErrHandler:
    put this after application.screenupdating = true statement:

    Code:
    Exit Sub
    ErrHandler:
    If Err.Number = 1004 Then MsgBox "This name already exists, please edit cell ""B28"" and provide a new name"
    will that work for you?
    Last edited by Danmc; Jul 19th, 2019 at 02:28 PM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,247
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code that stops and alerts user of error

    How about
    Code:
    Sub makeSheets()
       Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
       Set sh1 = Sheets("Template")
       Set sh2 = Sheets("Summary")
       Application.ScreenUpdating = False
       ActiveWorkbook.Sheets("Template").Visible = True
       For Each c In sh2.Range("B28", sh2.Cells(Rows.Count, 2).End(xlUp))
          If Evaluate("isref('" & c.Value & "'!A1)") Then
             MsgBox "Sheet " & c.Value & " already exists"
             Exit Sub
          End If
          sh1.Copy After:=Sheets(Sheets.Count)
          ActiveSheet.Name = c.Value
       Next
       ActiveWorkbook.Sheets("Template").Visible = False
       Sheets("Summary").Select
       Application.ScreenUpdating = True
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code that stops and alerts user of error

    You guys are the best!!!! Both Solutions worked like a charm. Thanks a bunch for your help.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,247
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code that stops and alerts user of error

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •