Hi,
I am creating a user form with a couple of list boxes. When you select a line of data in the first listbox, the second listbox should populate with comments based on a unique identifier. This works fine when you run the first bit of code below on its own, but if I filter the data in the first list box, I suddenly get the 'Method of Range Class Failed' debug.
I have tried to use advanced filter and auto filter, but both seem to get the same error. I have tried selecting and activating the sheet before applying the filter but this does not help. When I step through the code, and it runs the Sheets("XXX").Activate, it does not activate the sheet. But I can not work out why.
My userform is opened as VBModeless.
Can anyone help me work out why this is happening and how to stop it?
First piece of code, which runs fine on its own.
This is the piece of code which if it is run first, makes the above code error.
Thanks in advance for your help!
I am creating a user form with a couple of list boxes. When you select a line of data in the first listbox, the second listbox should populate with comments based on a unique identifier. This works fine when you run the first bit of code below on its own, but if I filter the data in the first list box, I suddenly get the 'Method of Range Class Failed' debug.
I have tried to use advanced filter and auto filter, but both seem to get the same error. I have tried selecting and activating the sheet before applying the filter but this does not help. When I step through the code, and it runs the Sheets("XXX").Activate, it does not activate the sheet. But I can not work out why.
My userform is opened as VBModeless.
Can anyone help me work out why this is happening and how to stop it?
First piece of code, which runs fine on its own.
Code:
Sub PopulateComments()
Dim r As Integer
Dim filtercri As String
r = ListBox1.ListIndex
filtercri = ListBox1.List(r, 0)
ThisWorkbook.Sheets("ItemCommentsV").Range("A:D").Value = ""
ListBox2.RowSource = ""
ThisWorkbook.Sheets("Filter Values").Range("A2").Value = filtercri
ThisWorkbook.Sheets("ItemComments").Range("A:D").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=ThisWorkbook.Sheets("Filter Values").Range("A1:D2"), _
CopyToRange:=ThisWorkbook.Sheets("ItemCommentsV").Range("A:D")
ThisWorkbook.Sheets("ItemCommentsV").Range("C:C").NumberFormat = "dd/mm/yyyy hh:mm;@"
Dim row199 As Integer
If ThisWorkbook.Sheets("ItemCommentsV").Range("A3").Value = "" Then
row199 = 2
Else
row199 = ThisWorkbook.Sheets("ItemCommentsV").Range("A1").End(xlDown).Row
End If
ThisWorkbook.Sheets("ItemCommentsV").Activate
With ListBox2
.ColumnHeads = True
.ColumnCount = 4
.ColumnWidths = "0;0;50;50"
.RowSource = ThisWorkbook.Sheets("ItemCommentsV").Range("A2:D" & row199).Address
End With
ThisWorkbook.Sheets("ItemComments").AutoFilterMode = False
End Sub
This is the piece of code which if it is run first, makes the above code error.
Code:
Sub ApplyFilter()
Application.ScreenUpdating = False
'Windows("Technical Team Backlog.xlsm").Visible = True
ThisWorkbook.Sheets("Current Sprint").AutoFilterMode = False
ThisWorkbook.Sheets("Current Sprint").Range("A:X").AutoFilter Field:=22, Criteria1:=UserForm9.ComboBox1.Value
ThisWorkbook.Sheets("ListBoxSheet").Range("A1:AA50000").Clear
ThisWorkbook.Sheets("Current Sprint").AutoFilter.Range.Copy
ThisWorkbook.Sheets("ListBoxSheet").Range("A1").PasteSpecial xlPasteValues
ThisWorkbook.Sheets("ListBoxSheet").Range("O:O").NumberFormat = "dd/mm/yyyy"
ThisWorkbook.Sheets("Current Sprint").AutoFilterMode = False
ThisWorkbook.Sheets("ListBoxSheet").Activate
If ThisWorkbook.Sheets("Listboxsheet").Range("A2") = "" And isclearing <> True Then
MsgBox "Nothing found in this sprint for " & UserForm9.ComboBox1.Value & "."
UserForm9.ListBox1.RowSource = ""
Exit Sub
End If
If ComboBox1.Value <> "" Then
Dim row156 As Integer
row156 = ThisWorkbook.Sheets("ListBoxSheet").Range("A1").End(xlDown).Row
With ListBox1
.ColumnHeads = True
.ColumnCount = 24
.ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;40;50;40;45;40;50;60;70;70"
.RowSource = ThisWorkbook.Sheets("ListBoxSheet").Range("A2:Y" & row156).Address
End With
TextBox1.Value = WorksheetFunction.Sum(ThisWorkbook.Sheets("ListBoxSheet").Range("T:T"))
TextBox2.Value = WorksheetFunction.SumIf(ThisWorkbook.Sheets("ListBoxSheet").Range("W:W"), "Complete", ThisWorkbook.Sheets("ListBoxSheet").Range("T:T"))
TextBox3.Value = Format(TextBox2.Value / TextBox1.Value, "Percent")
Else
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
End If
ThisWorkbook.Sheets("Current Sprint").AutoFilterMode = False
End Sub
Thanks in advance for your help!