Combobox issues on userform to update advanced filter

[rhombus4]

Have an advanced filter which works when I edit values in my criteria on sheet1.

However when I enter values in a combobox which transfers a value to a cell in the criteria area I get errors when the advanced filter runs

Unable to get the current region property if the range Class on line
ws.cells(2, 2).currentregion.offset(1).clear contents

When I change it to ws.range("B2:F100").clear contents I get Clear contents method of range Class failed

 

[rhombus4]

Still get issuses when running advanced fIlter (myFIlter) wither when i'm trying to clear the range before running filter or when i comment out the clear and run the filter
Unable to get the currentregion property of the Range class

and also got error with row_source - 'Run time error 381: Could not set the List property. Invalid property array index

This is my full code and all resides in the userform code

Private Sub UserForm_Initialize()
Sheet1.Range("V2:Z2").ClearContents
myFilter
Row_Sources

End Sub

Private Sub cmb1_Change()
Sheet1.Range("v2") = Me.cmb1.Value
myFilter
Row_Sources

End Sub

Private Sub cmb2_Change()
Sheet1.Range("w2") = Me.cmb2.Value
myFilter
Row_Sources

End Sub

Sub Row_Sources()
cmb1.RowSource = ""
Me.cmb1.List = Application.Range("rsCombo1").Value  'Run time error 381: Could not set the List property. Invalid property array index


cmb2.RowSource = ""
Me.cmb2.List = Application.Range("rsCombo2").Value

End Sub


Sub myFilter()
Application.ScreenUpdating = False
  Dim ws As Worksheet
  Set ws = Sheet1

  'ws.Cells(2, 11).CurrentRegion.Offset(1, 0).ClearContents ' Unable to get the currentregion property of the Range class
  'ws.Range("K2").CurrentRegion.Offset(1, 0).ClearContents ' Unable to get the currentregion property of the Range class
  'Range("K2").CurrentRegion.Offset(1, 0).ClearContents ' Unable to get the currentregion property of the Range class
  ' Range("K2:O1000") = "" ' Unable to get the currentregion property of the Range class. Also clears the combobox
     
'Unable to get the currentregion property of the Range class
  ws.Range("B1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=ws.Range("V1:Z2"), _
            CopyToRange:=ws.Range("K1:O1"), Unique:=False

'AdvancedFilter Method of the Range Class failed
  'ws.Range("A1:E1000").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=ws.Range("V1:Z2"), _
            CopyToRange:=ws.Range("K1:O1"), Unique:=False

With ws ' Removes duplicates to get new Row Sources for Combo Boxes
.Range("K1:K1000").RemoveDuplicates Columns:=1, Header:=xlYes
.Range("L1:L1000").RemoveDuplicates Columns:=1, Header:=xlYes
.Range("M1:M1000").RemoveDuplicates Columns:=1, Header:=xlYes
.Range("N1:N1000").RemoveDuplicates Columns:=1, Header:=xlYes
.Range("O1:O1000").RemoveDuplicates Columns:=1, Header:=xlYes
End With

Application.ScreenUpdating = True

End Sub

 

[RoryA]

Assuming that Sheet1 is the correct codename, is the sheet protected?

 

[rhombus4]

Sheet1 is correct codename and sheet isn't unprotected

I've just created a new workbook with Userform and just 2 comboboxes and ran again and still same error

The Row_Sources Code works, because it does populate the comboboxes on initialisation
The advanced filter works when I run it on the sheet

Private Sub UserForm_Initialize()
Sheet1.Range("V2:Z2").ClearContents
myFilter
Row_Sources

End Sub

Its when a combobox is changed. which passes the value to a cell, runs advanced filter and then runs row_sources

Private Sub ComboBox1_Change()
Sheet1.Range("v2") = Me.ComboBox1.Value
myFilter
Row_Sources

End Sub

Me.ComboBox1.List = Application.Range("rsCombo1").Value 'Run time error 381: Could not set the List property. Invalid property array index

Sub Row_Sources()
ComboBox1.RowSource = ""
Me.ComboBox1.List = Application.Range("rsCombo1").Value  'Run time error 381: Could not set the List property. Invalid property array index

ComboBox2.RowSource = ""
Me.ComboBox2.List = Application.Range("rsCombo2").Value

End Sub

 

[RoryA]

At the point the error occurs, can you run:

?Application.Range("rsCombo1").address

in the Immediate Window, or does that error too?

If it works, perhaps you are only getting one result, and List expects an array, so:

Sub Row_Sources()
ComboBox1.RowSource = ""
Me.ComboBox1.List = ArrayFromRange(Application.Range("rsCombo1"))
ComboBox2.RowSource = ""
Me.ComboBox2.List = ArrayFromRange(Application.Range("rsCombo2"))
End Sub

Function ArrayFromRange(rg as Excel.Range)
With rg
   If .count > 1 then
      ArrayFromRange = .Value
   Else
      ArrayFromRange = Array(.Value)
    End If
End With
End Function

 

[rhombus4]

When i entered a value in Combobox1 and got the Error I hit debug and entered in immediate window
?Application.Range("rsCombo1").address
$K$2
?Application.Range("rsCombo2").address
$L$2:$L$5

K2 was the value of what I entered in Combobox 1 and L2:L5 are the results of the Advanced Filter

 

[RoryA]

Can you try the updated code above then?

 

[rhombus4]

Thanks

Updated and added extra comboboxes and it seems to update filter and update lists for comboboxes as well

maybe it was all about the one result that was chosen in the combobox that was causing the issue?

So it it better to use lists than rowsources for all controls e,g. comboboxes and listboxes

 

[RoryA]

I am not a fan of linking controls directly to ranges - they tend to have their events triggered when you don't want them to. The only time I would ever use Rowsource (which isn't supported at all on Macs by the way) would be if I need column headers in a listbox.

 

[rhombus4]

Ok. Thanks for your help today Rory.

Marked as Solved

 

[rhombus4]

just noticed when the advanced filter runs and one of the values in the columns of the dynamic range is blank i get an error
Run time error 1004: Method Range of object application failed

Sub Row_Sources()
ComboBox1.RowSource = ""
Me.ComboBox1.List = Application.Range("rsCombo1").Value  'Run time error 381: Could not set the List property. Invalid property array index

ComboBox2.RowSource = ""
Me.ComboBox2.List = Application.Range("rsCombo2").Value '1004: Method Range of object Application failed if there are no entries in Dynamic Range

End Sub