CODEWITHSUNDEEP
vbaexcel
Maxim Srour
Maxim Srour

Reputation: 157

Check if variant is Null or an array

I have a dynamic range that is being used to set a combobox in vba.

The range starts as A3 (which will contain nothing to start) and goes all the way to A3:A9999, depending on how many elements are in the range.

The code then pulls in the data from the range and stores it in a local variant.

My code in VBA is this:

If tempj <> Null Then
    cmb_JobNum.List = tempj
End If

When there are 0 elements in the array, tempj = Null, so it does not attempt to set the list.

When there is 1 element in the array, tempj = [Value of cell], so it will set the list to that single element.

When there is 2 or more elements in the array, tempj is now an array, so trying to equate it to a single element throws a 'type mismatch' error. I have no clue how to update the code so that it doesn't get caught out by that error, since every time that equate is run it will crash.

Upvotes: 0

Views: 2108

Answers (3)

QHarr
QHarr

Reputation: 84465

Here is an example using a dynamic named range to set the fill

Option Explicit

Public Sub test()

    With ThisWorkbook.Worksheets("Sheet6") '<== change as appropriate
        .ComboBox1.ListFillRange = .Range("dynRange").Address
    End With

End Sub

dynRange formula added via name manager (Ctrl + F3)

=OFFSET(Sheet6!$A$3,0,0,COUNTA(Sheet6!$A:$A),1)

Using a worksheet change event to automatically update the combobox:

You could tie this into a Worksheet_Change event on the range A3:A9999 to update automatically the Combobox.

If tying to an event in the code pane of the sheet containing the combobox you could have the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A3:A9999")) Is Nothing Then
        Application.EnableEvents = False
        Me.ComboBox1.ListFillRange = ThisWorkbook.Worksheets("Sheet6").Range("dynRange").Address
        Application.EnableEvents = True
    End If
End Sub

Example code run:

Test run

Code pane for sheet containing Combobox:

Code pane

Note:

This is assuming an ActiveX combobox but can easily be update for a Form Control ComboBox.

For a form control swop out lines and use:

 With Shapes("Drop Down 2").ControlFormat '<== change to appropriate name
     .ListFillRange = ThisWorkbook.Worksheets("Sheet6").Range("dynRange").Address
 End With

Edit: For UserForm combobox you can populate in the initialize e.g.

Private Sub UserForm_Initialize()
    cb1.RowSource = Sheet1.Range("dynRange").Address
End Sub

Upvotes: 1

Maxim Srour
Maxim Srour

Reputation: 157

Figured it out

If VarType(tempj) <> 0 Then
    If VarType(tempj) = 8 Then
        cmb_JobNum.AddItem tempj
    Else
        cmb_JobNum.List = tempj
    End If
End If

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

You could try like this:

Dim i As Long
For i = LBound(tempj) To UBound(templ)
    cmb_JobNum.AddItem tempj(i)
Next

This code will loop through your array and add every element in it to the combobox. Thus, if array is empy, then no elelements will be added, when there's >0 elements, then all of them will be added.

Upvotes: 1

Related Questions