Populating a ComboBox with a Defined Range from another Sheet

[Izran]

I'm trying to change the contents of a second list box (Service_Entry)
when a selection is made in an initial list box (Commodity_Entry). I
have a dynamic defined range name AIR_SERVICE_LIST, defined as follows
in Excel:

=OFFSET(Air_List!$C$4,0,0,COUNTA(Air_List!$C$4:$C$100),1)

I used this to allow for later entries into the list. My problem is I
am trying to take AIR_SERVICE_LIST as a range and drop it into
SERVICE_BOX.ListFillRange when AIR is selected in the COMMODITY BOX.
Here is the code I've been playing with:


Private Sub COMMODITY_BOX_Change()

If Sheets("Commodity_Entry").Range("COMMODITY") = "Air" Then _
Call Air_Service_List

End Sub



Sub Air_Service_List()

Sheets("Commodity_Entry").SERVICE_BOX.ListFillRange =
Sheets("Air_List").Range("AIR_SERVICE_LIST")

End Sub


I keep getting a type mismatch. I'm guessing that somehow its not
passing the range, but maybe the contents. Any help would be
appreciated.

 

[Tom Ogilvy]

Sub Air_Service_List()

Sheets("Commodity_Entry").SERVICE_BOX.ListFillRange = _
Sheets("Air_List").Range("AIR_SERVICE_LIST").Address(External:=True)

End Sub

 

[Izran]

As usual, you're a genious Tom!!! TYVM.

Regards,
Mike Manzi