hi
a userform has this code
Option Explicit
Private Sub ComboBox1_Change()
Dim idx As Long
Dim rsource As Long
Dim rowsource As Long
Dim i As Long
Dim rw As Long
Dim arrIDS As Variant
Dim arrFilteredIDS As Variant
idx = ComboBox1.ListIndex
If idx <> -1 Then
ListBox1.Clear
With Sheets("repairs")
With .Range("a2", .Range("a" & Rows.Count).End(xlUp))'changed to("f2", .Range("f" ect
arrIDS = Evaluate("TRANSPOSE(repairs!" & .Address & "&""-""&ROW(" & .Address & "))")
End With
End With
arrFilteredIDS = Filter(arrIDS, ComboBox1.Value, True)
For i = LBound(arrFilteredIDS) To UBound(arrFilteredIDS)
rw = Replace(arrFilteredIDS(i), ComboBox1.Value & "-", "")
With ListBox1
.AddItem ComboBox1.Value
' .List(.ListCount - 1, 1) = Sheets("repairs").Range("a" & rw).Value
.List(.ListCount - 1, 2) = Sheets("repairs").Range("b" & rw).Value
.List(.ListCount - 1, 3) = Sheets("repairs").Range("c" & rw).Value
.List(.ListCount - 1, 4) = Sheets("repairs").Range("i" & rw).Value
.List(.ListCount - 1, 5) = Sheets("repairs").Range("j" & rw).Value
.List(.ListCount - 1, 6) = Sheets("repairs").Range("f" & rw).Value
.List(.ListCount - 1, 7) = Sheets("repairs").Range("l" & rw).Value
.List(.ListCount - 1, 8) = Sheets("repairs").Range("n" & rw).Value
Dim Rownum As Long
.ColumnWidths = "1.0 in;0;3.0in;1.0 in;1.5 in;1.5 in;1.5 in"
End With
Next i
End If
End Sub
Private Sub TextBox1_Change()
'Me.TextBox1.Value = ListBox6.ListCount
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub UserForm_Initialize()
Call RemoveCaption(Me)
With Application
Width = .Width
Height = .Height
End With
Dim dicIDs As Object
Dim rngIDs As Range
Dim cl As Range
With Sheets("repairs")
Set rngIDs = .Range("a2", .Range("a" & Rows.Count).End(xlUp)) 'changed to ("f2", .Range("f" ect
End With
Set dicIDs = CreateObject("Scripting.Dictionary")
For Each cl In rngIDs.Cells
If Not dicIDs.exists(cl.Text) Then
dicIDs.Add cl.Value, cl.Text
End If
Next cl
ComboBox1.List = dicIDs.keys
End Sub
this works fine if range is a2 but if I change it to f2 it has error457 (this key is already associated with an element of this collection)can anyone tell me why
it will not work please
cheers colin
Bookmarks