combobox.ListFillRangeTrouble in VBA

[DogLover]

I am trying to use 2 different Listfill Ranges in a combo box depending on
whether a check box is true or false. The code blows up on the last line of
code ComboBoxDept.ListFillRange = NewFillRange. Can anyone help me figure
out what I'm doing wrong?


Dim NewFillRange As Range
If CheckBoxEntity.Value = True Then Set NewFillRange =
Worksheets("Demo").Range("DemoDeptByEntity")
If CheckBoxEntity.Value = False Then Set NewFillRange =
Worksheets("Demo").Range("DemoDept")
ComboBoxDept.ListFillRange = NewFillRange

 

[joel]

Try these changes

Dim NewFillRange As Range
If CheckBoxEntity.Value = True Then
Set NewFillRange = Worksheets("Demo").Range("DemoDeptByEntity")
Else
Set NewFillRange = Worksheets("Demo").Range("DemoDept")
end if
ComboBoxDept.ListFillRange = NewFillRang

 

[JLGWhiz]

Try it this way:

Dim NewFillRange As Range
If CheckBoxEntity.Value = True Then
Set NewFillRange = Worksheets("Demo").Range("DemoDeptByEntity")
Else
Set NewFillRange = Worksheets("Demo").Range("DemoDept")
End If
ComboBoxDept.ListFillRange = NewFillRange

 

[FSt1]

hi
not sure but excel may think you are trying to assign the values of your
named ranges to the listfillrange. you may be getting a type mismatch error.
guessing.
try this......

ComboBoxDept.ListFillRange = NewFillRange.Address

regards
FSt1

 

[Dave Peterson]

I bet that checkboxentity is set to be checked or unchecked. (You're not using
triplestate = true).

Dim NewFillRange As Range
If me.CheckBoxEntity.Value = True Then
Set NewFillRange = Worksheets("Demo").Range("DemoDeptByEntity")
else 'it has to be false
Set NewFillRange = Worksheets("Demo").Range("DemoDept")
end if
me.ComboBoxDept.ListFillRange = NewFillRange.address(external:=true)