I'm looking to add a checkbox that displays any "Not Found" items. When this I would like to edit the Listitem
My code currently is working when I open userform2 and edit the Listbox without checking checkbox1.
However, when I checkbox1 is true, it correctly displays the only "Not Found" but when I go to edit the list item I receive run-time error 1004 method range of object _global fail
on:
Set rCell = Range(.RowSource).Resize(1).Offset(.ListIndex)
my full code: for userform2
Private Sub ListBox2_Click()
TextBox1.Enabled = True
TextBox1.Value = ListBox2.Value
End Sub
Private Sub TextBox1_Change()
Dim rCell As Range
With ListBox2
Set rCell = Range(.RowSource).Resize(1).Offset(.ListIndex)
rCell.Value = TextBox1.Value
End With
End Sub
Private Sub CheckBox1_Click()
OptimizedMode True
If userform2.CheckBox1.Value = True Then
Worksheets("Table").Range("A1").AutoFilter Field:=1, Criteria1:="Not Found", Operator:=xlOr, Criteria2:="="
userform2.ListBox2.RowSource = vbNullString
userform2.ListBox2.ColumnHeads = False
Dim rng As Range
Dim Cel1 As Range
Dim LR As Long
Dim ws As Worksheet
Set ws = Sheets("Table")
With ws
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible)
With userform2.ListBox2
.ColumnCount = 1
For Each Cel1 In rng
.AddItem CStr(Cel1.Value)
.List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
Next Cel1
End With
End With
End If
If CheckBox1.Value = False Then
With userform2.ListBox2
.RowSource = "Table!A2:A1048576"
End With
End If
OptimizedMode False
End Sub
You use CheckBox1_Click event to control ListBox2.RowSource. If CheckBox1 is TRUE you clear RowSource and then add items to the list.
When RowSource is cleared Range(.RowSource) is the same as Range("") which of course errors.
Under these conditions, you'll need to devise another method to determine which row the ListBox refers to.