CODEWITHSUNDEEP
excelvba
Salman Khan
Salman Khan

Reputation: 141

Code to show message box if there is no data in table column

I have a userform with 2 comboboxes.

Both comboboxes are populated from the tables present in the sheet.

Here is my code.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet, tbl As ListObject, rng As Range

    Set ws = Sheets("Summary of Accounts")
    Set tbl = ws.ListObjects("groupheads")
    Set rng = tbl.ListColumns(3).DataBodyRange

    Me.cmb1.Clear
    Me.cmb2.Clear

    With cmb1
        .AddItem "Debit"
        .AddItem "Credit"
    End With


    "If rng = vbNullString Then"
        MsgBox ("Please create the Group Head Account First")
    Exit Sub

    End If

End Sub

The error occurs at the quoted line as object variable not defined. What i want is if there is no data in the table column, the msg box should be appeared which I have shown in the above code.

Upvotes: 2

Views: 1586

Answers (1)

user4039065
user4039065

Reputation:

If a ListObject table has no data in the first row of the .DataBodyRange property then setting a range type variable to it will result in Nothing.

If rng Is Nothing Then
    MsgBox ("Please create the Group Head Account First")
    Exit Sub
End If

Yes, it looks like there is an empty row there but the .DataBodyRange is actually nothing until it gets at least one value in one of the cells.

Upvotes: 2

Related Questions