Why wont it do?

[thorpyuk]

Dim bfr As Long
bfr = ComboBox1.Value
ComboBox1.Value = bfr: Exit Sub

how come this gives me a "cant change property" error!?

The code is inside my combobox change event...

 

[thorpyuk]

Norie: bah humbug! Dost thou suggest there are errors in my code? Surely not!

Rorya: I tried searching backwards, but the effect seemed to be the same :<

But.... there's about 3 processes that populating this particular userform goes through.

1) find/additem - populates listbox
2) scripting dictionary to split it by column 2
2) sorting - sort it.

code here:

Private Sub UserForm_Initialize()

ComboBox1.List = Array("Project", "Sub-Task")
ComboBox1.Value = "Project"

    Dim Sh As Worksheet
    Dim rng As Range
    Dim R As Integer
    Dim Cell As Range
    Dim FirstAddr As String
    'If ComboBox1.Value = "Sub-Task" Then GoTo 20
    Set Sh = Worksheets("Changes")
    'Set Rng = Sh.Range("A1").CurrentRegion
    
    LR = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Sh.Range("A2:J" & LR)
    
    R = 0
    ListBox1.Clear
    With rng
        Set Cell = .Find(What:=ComboBox1.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
        If Not Cell Is Nothing Then
            FirstAddr = Cell.Address
            Do
                With ListBox1
                    .AddItem (Cell.EntireRow.Cells(1, 1).Value)
                    .List(R, 1) = Cell.EntireRow.Cells(1, 2).Value
                    .List(R, 2) = Cell.EntireRow.Cells(1, 3).Value
                    .List(R, 3) = Cell.EntireRow.Cells(1, 4).Value
                    .List(R, 4) = Cell.EntireRow.Cells(1, 5).Value
                    .List(R, 5) = Cell.EntireRow.Cells(1, 6).Value
                    .List(R, 6) = Cell.EntireRow.Cells(1, 7).Text
                    .List(R, 7) = Cell.EntireRow.Cells(1, 8).Text
                    .List(R, 8) = Cell.EntireRow.Cells(1, 9).Value
                    .List(R, 9) = Cell.EntireRow.Cells(1, 10).Text
                    R = R + 1
                End With
            Set Cell = .FindNext(Cell)
            Loop While Not Cell Is Nothing And Cell.Address <> FirstAddr
        End If
    End With
Set Sh = Nothing
Set rng = Nothing
Set Cell = Nothing

Dim a, z As Long
Set dic = CreateObject("scripting.dictionary")
With Sheets("Changes")
    a = ListBox1.List
    'a = .Range("b1", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1).Resize(, 10).Value
End With

For z = 2 To UBound(a, 1)
    If Not dic.exists(a(z, 1)) Then
        ReDim w(1 To 10, 1 To 1)
        For zz = 1 To 10: w(zz, 1) = a(z, zz - 1): Next
        dic.Add a(z, 1), w
    Else
        w = dic(a(z, 1))
        ReDim Preserve w(1 To 10, 1 To UBound(w, 2) + 1)
        For zz = 1 To 10: w(zz, UBound(w, 2)) = a(z, zz - 1): Next
        dic(a(z, 1)) = w
    End If
Next
ComboBox2.List = dic.keys
ComboBox2.AddItem "All"
ComboBox2.Value = Sheets("Calendar").Range("E3").Value

If ComboBox2.ListIndex = -1 Then Exit Sub

With ListBox1
    .Column = dic(ComboBox2.Value)
End With

Label10_Click

End Sub

label10_click code which does the sorting:

Private Sub Label10_Click()
    Dim i As Long
    Dim j As Long
    Dim sTemp As String
    Dim sTemp2 As String
    Dim sTemp3, sTemp10 As String
    Dim sTemp4, stemp5, sTemp6, sTemp7, sTemp8, sTemp9 As String
    Dim LbList As Variant
    
    'Store the list in an array for sorting
    LbList = UserForm7.ListBox1.List
    
    'Sort the array on the first value
    For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
        For j = i + 1 To UBound(LbList, 1)
            If CDate(LbList(i, 9)) < CDate(LbList(j, 9)) Then
                'Swap the first value
                sTemp = LbList(i, 0)
                LbList(i, 0) = LbList(j, 0)
                LbList(j, 0) = sTemp
                'Swap the second value
                sTemp2 = LbList(i, 1)
                LbList(i, 1) = LbList(j, 1)
                LbList(j, 1) = sTemp2
                'Swap the third value
                sTemp3 = LbList(i, 2)
                LbList(i, 2) = LbList(j, 2)
                LbList(j, 2) = sTemp3
                'Swap the fourth value
                sTemp4 = LbList(i, 3)
                LbList(i, 3) = LbList(j, 3)
                LbList(j, 3) = sTemp4
                'Swap the fifth value
                stemp5 = LbList(i, 4)
                LbList(i, 4) = LbList(j, 4)
                LbList(j, 4) = stemp5
                'Swap the sixth value
                sTemp6 = LbList(i, 5)
                LbList(i, 5) = LbList(j, 5)
                LbList(j, 5) = sTemp6
                'Swap the seventh value
                sTemp7 = LbList(i, 6)
                LbList(i, 6) = LbList(j, 6)
                LbList(j, 6) = sTemp7
                'Swap the eigth value
                sTemp8 = LbList(i, 7)
                LbList(i, 7) = LbList(j, 7)
                LbList(j, 7) = sTemp8
                'Swap the ninth value
                sTemp9 = LbList(i, 8)
                LbList(i, 8) = LbList(j, 8)
                LbList(j, 8) = sTemp9
                'Swap the tenth value
                sTemp10 = LbList(i, 9)
                LbList(i, 9) = LbList(j, 9)
                LbList(j, 9) = sTemp10
            End If
        Next j
    Next i
    
    'Remove the contents of the listbox
    UserForm7.ListBox1.Clear
    'Repopulate with the sorted list
    UserForm7.ListBox1.List = LbList
End Sub

:>

 

[RoryA]

Clue:

Set Cell = .FindNext(Cell)

 

[thorpyuk]

Clue:

Set Cell = .FindNext(Cell)

        Set Cell = .FindNext(What:=ComboBox1.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)

Named argument not found

And your avater is scaring me :>

 

[RoryA]

Set Cell = .FindNext(Cell)

can you guess why it's not going backwards? FindPrevious is what you want.
And that will be the last help you get for insulting my daughter...