Use of if condition with table

Question

How I can add a new sheet in the excel workbook if I add a new row in the table? Please help me? I am trying this code. If Table 2 resizes ( adding a row) then new sheet otherwise message box.

Sub Message_box()

ActiveSheet.ShowDataForm

Dim myif As String

myif = ActiveSheet.ListObjects("Table2").ResizeRange

If myif = "true" Then

    Sheets("Sample Table").Select
    Cells.Select
    Selection.Copy
    Sheets("All Loans").Select
    Sheets.Add After:=ActiveSheet
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
    
Else

    MsgBox "Please, add loan detail in Form?"
    

End If

End Sub

Either use a macro to add the rows, and simultaneously add sheets. Or run a Worksheet.Change event where you check the size of the table. If you want more specific help, you need to give it a bit more effort. — Christofer Weber, May 14 '21 at 16:56
I have record macro and it's giving some type of debugging, I want a code using the IF condition, If a new row added to my table then a new sheet should be open every time, if not then the message box "Enter a data in the table" — amin asif, May 14 '21 at 18:07
`.ResizeRange` isn't valid AFAIK. You can use `.ListRows.Count` to return the number of rows, but to use it I guess you'd need to keep track of the previous value to compare it to. And the obligatory link to [How to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) — Christofer Weber, May 14 '21 at 18:42

score 0 · Accepted Answer · answered May 14 '21 at 19:01

Something like this would be similar to what you're attempting, but it's not a great approach (what if the user adds a new row and also deletes an existing one?)


Sub Message_box()

    Dim lo As ListObject, r As Long
    
    Set lo = ActiveSheet.ListObjects("Table2")
    r = lo.ListRows.Count
    
    ActiveSheet.ShowDataForm
    
    If lo.ListRows.Count > r Then
        ThisWorkbook.Sheets("Sample Table").Copy _
           after:=ThisWorkbook.Sheets("All Loans")
    Else
        MsgBox "Please, add loan detail in Form?"
    End If

End Sub

Use of if condition with table

1 Answers1