combobox.ListFillRangeTrouble in VBA

  • Thread starter Thread starter DogLover
  • Start date Start date
D

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
 
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
 
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
 
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)
 
Back
Top