Code to restrict userform from loading if there is

Question

i have developed a small userform with one combobox in it. This combobox is filled with the range in worksheet upon loading the userform. What i need is, if there is no value to load in combobox, it should display an error "Nothing is there to load". This userform is loaded through a small button.

Below is my userform initialization code:

Private Sub UserForm_Activate()
'LOAD THE LIST OF ACCOUNTS

'ASSIGNING THE VARIABLES
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range

'Declaring the Variables
Set ws = Sheets("Cash and Bank Account Details")
Set tbl = ws.ListObjects("newaccount")
Set rng = tbl.ListColumns(3).DataBodyRange

'Adding the Items in Combo Box
For Each rng In rng
ComboBox1.AddItem rng.Value
Next
ComboBox1.ListIndex = 0
End Sub

Kindly review and help me with the query.

Thanks.

Answer 1

I am guessing that you are looking for something like this:

If UserForm1.ComboBox1.ListCount = 0 Then
    MsgBox "Nothing is there to load"
Else
    UserForm1.Show
End If

Just before showing the form to the user you can check if there is anything in the ComboBox1 worth showing.

Alternatively, you can even check before initializing the form:

If Application.WorksheetFunction.CountA(Sheets("Cash and Bank Account Details").Listobjects("newaccount").ListColumns(3).DataBodyRange) = 0 Then
    MsgBox "Nothing is there to load"
Else
    'Initialize and fill the form
    UserForm1.Show
End If