desperately put validation if statement in VBA
Results 1 to 3 of 3

Thread: desperately put validation if statement in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2009
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default desperately put validation if statement in VBA

    Hi sorry about this question. It seems very easy but I've spent several hour to bang my head and I can't figure it out.

    I want to insert multiple worksheet with VBA by refer range of cell and use text from those range as a sheet name. I did search and found VBA code that I can use.

    However, I want to add a validation -- if the sheet name is exist, do not add any sheet. just skip it but I can't insert the simple IF statement in VBA due to lack of VBA knowledge. Moreover, I try to move On Error Resume Next above Sheets.Add and hope On Error GoTo 0 would negate the Sheets.Add step. However, VBA still create sheet with default name (e.g. Sheet15) which is not what I expect.

    Code:
    Sub AddSheets()
    'Updateby Extendoffice 20161215
        Dim xRg As Excel.Range
        Dim wSh As Excel.Worksheet
        Dim wBk As Excel.Workbook
        Set wSh = ActiveSheet
        Set wBk = ActiveWorkbook
        Application.ScreenUpdating = False
        For Each xRg In wSh.Range("E2:E9")
            With wBk
            'if the sheet with that name exist, should not add sheet.
            
            
            'else add sheet.
            
                .Sheets.Add after:=.Sheets(.Sheets.Count)
                On Error Resume Next
                ActiveSheet.Name = xRg.Value
                If Err.Number = 1004 Then
                  Debug.Print xRg.Value & " already used as a sheet name"
                End If
                On Error GoTo 0
            End With
        Next xRg
        Application.ScreenUpdating = True
    End Sub
    could someone point me what I misunderstand here?

    Thank you very much
    Last edited by Flicker; Jul 19th, 2019 at 01:00 AM. Reason: just realize that I leave with incomplete sentence.

  2. #2
    New Member
    Join Date
    Feb 2009
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: desperately put validation if statement in VBA

    It's seems working now. but any suggestions or improvements always welcome.

    Code:
    Sub AddSheets()
    'Updateby Extendoffice 20161215
        Dim xRg As Excel.Range
        Dim wSh As Excel.Worksheet
        Dim wBk As Excel.Workbook
        
        Set wSh = ActiveSheet
        Set wBk = ActiveWorkbook
        Application.ScreenUpdating = False
        
        For Each xRg In wSh.Range("E2:E9")
            
            For i = 1 To Worksheets.Count
                If Worksheets(i).Name = xRg.Value Then
                    exists = True
                End If
            Next i
                
            If Not exists Then
                
                With wBk
                    .Sheets.Add after:=.Sheets(.Sheets.Count)
                    On Error Resume Next
                    ActiveSheet.Name = xRg.Value
                    If Err.Number = 1004 Then
                      Debug.Print xRg.Value & " already used as a sheet name"
                    End If
                    On Error GoTo 0
                End With
                
            End If
                
        exists = False
        Next xRg
        Application.ScreenUpdating = True
    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: desperately put validation if statement in VBA

    This assumes that the values in E are valid sheet names
    Code:
    Sub Flicker()
       Dim Cl As Range
       
       For Each Cl In Range("E2:E9")
          If Cl <> "" Then
             If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
                Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
             End If
          End If
       Next Cl
    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

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
  •