Please could you help with Named Table

[Eric Penfold]

I need code to stop running when a Table called Table2 is already there. Some reason I can`t get the Function to work with main code?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Rng
    Dim Result As Integer
    
    Call DeleteTbl
    
    If Selection.Count > 1 Then Exit Sub
    Set Rng = Range("J:J")
    If Not Intersect(Target, Rng) Is Nothing Then
        
        Result = MsgBox("Need to Send Depot Email Yes/No", vbInformation + vbYesNo, "Send Email Yes/No")
        
        Select Case Result
            Case vbYes
                Call Email_Depot
            Case vbNo
                Exit Sub
        End Select
        
    End If
    
    Range("A" & Cells.Rows.Count).End(xlUp).Select
    
    Call CreateTbl
    
If TableExists = True Then

        Exit Sub
        
End Sub
Function TableExists(tableName As String, sheetName As String) As Boolean

    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets("Data")

    Dim tbl As ListObject

    With targetSheet
        For Each tbl In .ListObjects
            If tbl.Name = "Data2" Then TableExists = True
        Next tbl
    End With

End Function

 

[jkpieterse]

The call to TableExists in the SelectionChange event does not have any arguments:

If TableExists = True Then

I think this should look more like this:

If TableExists("Data2", "Data") Then

In addition, your function is not using its arguments, I think it should read:

Function TableExists(tableName As String, sheetName As String) As Boolean

    Dim tbl As ListObject

    With Worksheets(sheetName)
        For Each tbl In .ListObjects
            If tbl.Name = tableName Then 
               TableExists = True
               Exit For
           End If
        Next tbl
    End With

End Function

 

[Eric Penfold]

This Function works fine but when I run it the code keeps going back to Call "CreateTbl". But I need it to Exit Sub after it finds the table in the sheet.

 

[jkpieterse]

In that case, change this bit:

    Call CreateTbl
    
If TableExists = True Then

To:

If Not TableExists Then
    CreateTbl
End If

 

[Eric Penfold]

All works well now but what I don`t understand is why it keeps highlighting the table even when the code is complete??

 

[jkpieterse]

Place a breakpoint on the first line of code (click on the line of code and press the F9 key). Then allow the code to run by selecting a different cell. Use the F8 key to step through the code. Make sure you place the Excel window next to the VBA Editor so you can see what is happening.