UserForm Listbox Clearing Before Repopulating

N

nygex

New Member
Joined
Jan 19, 2010
Messages
10
I'm stuck on what I'm doing wrong here and hoping there is any easy solution. The code works fine without the .clear but I'm just adding to the list each time I select a new option button and I want to clear the old data first.

VBA Code: 
Private Sub LoadData1()
Dim ws As Worksheet
Set ws = Worksheets("Detail")
    
    ListBox1.Clear '<<===This causes a problem no matter where I place it.
    'I even created a separate button just to clear and still creates an issue when running
    'the below

    For irow = 18 To 850
    If Trim(ws.Range("AH" & irow).Value) = 1 Then
        With ListBox1
    .ColumnCount = 5
    .ColumnWidths = "40;80;80;80;5"
    .AddItem
    .List(i, 0) = Trim(ws.Range("O" & irow).Value) '<<===I get the error here
    .List(i, 1) = Trim(ws.Range("P" & irow).Value)
    .List(i, 2) = Trim(ws.Range("Q" & irow).Value)
    .List(i, 3) = Trim(ws.Range("S" & irow).Value)
    .List(i, 4) = "*"
    
    i = i + 1
            
        End With
    End If
Next irow

    Dim Arg1 As Range
    Dim Arg2 As Range
    
    Set Arg1 = ws.Range("AQ18:AQ847")
    Set Arg2 = ws.Range("AH18:AH847")
     
    txtSalaryAmt.Value = Format(Application.WorksheetFunction.SumIfs(Arg1, Arg2, 1) * 12, "#,###")

OptionButton3.Value = True

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Thanks for the reply.

Run-time error 381
Could not set the List Property. Invalid property array index

I only get the error after I clear the listbox. Code runs fine otherwise
 
Upvote 0
Thanks a lot for that and I can see what you are saying. Interestingly, I don't get that hang and it just immediately gives the error, like it just bypasses listox1.clear. I can definitely see how yours is clearing the listbox first (mine's not).

I'm calling the two from option buttons as follows. So, it will load fine the first time I select an option but then errors when I want to clear that data and replace with the other load. If I remove listbox1.clear then it just keeps adding and adding.

VBA Code: 
Private Sub OptionButton4_Click()

LoadData1

End Sub

Private Sub OptionButton5_Click()

LoadData2

End Sub

Probably not the most efficient as I don't use VBA that much so your help is greatly appreciated.
 
Upvote 0
Post all of your code related to the listbox. I just ran the procedure back to back and it worked. Maybe your missing something simple. I do that quite regularly. :)
 
Upvote 0
Haha, ok. Don't be too harsh on me. Below is the entire userform. I just quickly created this userform to move people off of one list and place on another to see the salary impact of releasing them :/ 1 is lowest performer and 2 is the next lowest, for a little context. I'll add in some more code to flag them in the detail spreadsheet at the same time but need to get this sorted first. Thanks again for looking.

VBA Code: 
Dim i As Integer

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = True
    Next i
End If

If CheckBox1.Value = False Then
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = False
    Next i
End If

End Sub

Private Sub CheckBox2_Click()

If CheckBox2.Value = True Then
    For i = 0 To ListBox2.ListCount - 1
        ListBox2.Selected(i) = True
    Next i
End If

If CheckBox2.Value = False Then
    For i = 0 To ListBox2.ListCount - 1
        ListBox2.Selected(i) = False
    Next i
End If

End Sub

Private Sub CommandButton1_Click()

For i = 0 To ListBox1.ListCount - 1

    If ListBox1.Selected(i) = True Then
    With ListBox2
    .AddItem
    .ColumnCount = 3
    .ColumnWidths = "40;80;80"
     ListBox2.Column(0, (ListBox2.ListCount - 1)) = ListBox1.Column(0, i)
     ListBox2.Column(1, (ListBox2.ListCount - 1)) = ListBox1.Column(1, i)
     ListBox2.Column(2, (ListBox2.ListCount - 1)) = ListBox1.Column(2, i)
    End With
    End If

Next

End Sub

Private Sub CommandButton2_Click()

Dim counter As Integer
counter = 0

For i = 0 To ListBox2.ListCount - 1
    If ListBox2.Selected(i - counter) Then
        ListBox2.RemoveItem (i - counter)
        counter = counter + 1
    End If
Next i

CheckBox2.Value = False

End Sub

Private Sub OptionButton1_Click()

ListBox1.MultiSelect = 0
ListBox2.MultiSelect = 0

End Sub

Private Sub OptionButton2_Click()

ListBox1.MultiSelect = 1
ListBox2.MultiSelect = 1

End Sub

Private Sub OptionButton3_Click()

ListBox1.MultiSelect = 2
ListBox2.MultiSelect = 2

End Sub

Private Sub OptionButton4_Click()

LoadData1

End Sub

Private Sub OptionButton5_Click()

LoadData2

End Sub

Private Sub UserForm_Initialize()

'LoadData

End Sub

Private Sub LoadData1()

Dim ws As Worksheet
Set ws = Worksheets("Detail")
    
    'ListBox1.Clear '<<===This causes a problem no matter where I place it.
    'I even created a separate button just to clear and still creates an issue when running
    'the below

    For irow = 18 To 850
    If Trim(ws.Range("AH" & irow).Value) = 1 Then
        With ListBox1
        
    .ColumnCount = 5
    .ColumnWidths = "40;80;80;80;5"
    .AddItem
    .List(i, 0) = Trim(ws.Range("O" & irow).Value)
    .List(i, 1) = Trim(ws.Range("P" & irow).Value)
    .List(i, 2) = Trim(ws.Range("Q" & irow).Value)
    .List(i, 3) = Trim(ws.Range("S" & irow).Value)
    .List(i, 4) = "*"
    
    i = i + 1
            
        End With
    End If
Next irow

    Dim Arg1 As Range
    Dim Arg2 As Range
    
    Set Arg1 = ws.Range("AQ18:AQ847")
    Set Arg2 = ws.Range("AH18:AH847")
     
    txtSalaryAmt.Value = Format(Application.WorksheetFunction.SumIfs(Arg1, Arg2, 1) * 12, "#,###")

OptionButton1.Value = True

End Sub
Private Sub LoadData2()

Dim ws As Worksheet
Set ws = Worksheets("Detail")

    For irow = 18 To 850
    'ListBox1.Clear
    If Trim(ws.Range("AH" & irow).Value) = 2 Then
        With ListBox1
    .ColumnCount = 5
    .ColumnWidths = "40;80;80;80;5"
    .AddItem
    .List(i, 0) = Trim(ws.Range("O" & irow).Value)
    .List(i, 1) = Trim(ws.Range("P" & irow).Value)
    .List(i, 2) = Trim(ws.Range("Q" & irow).Value)
    .List(i, 3) = Trim(ws.Range("S" & irow).Value)
    .List(i, 4) = "*"
    
    i = i + 1
            
        End With
    End If
Next irow

    Dim Arg1 As Range
    Dim Arg2 As Range
    

    Set Arg1 = ws.Range("AQ18:AQ847")
    Set Arg2 = ws.Range("AH18:AH847")
    
    txtSalaryAmt.Value = Format(Application.WorksheetFunction.SumIfs(Arg1, Arg2, 2) * 12, "#,###")

OptionButton1.Value = True

End Sub
 
Upvote 0
Get ListBox.Clear out of your "For loop." As is, it's gonna clear your list on every iteration.

No-no...
VBA Code: 
    For irow = 18 To 850
    ListBox1.Clear

Yes-yes
VBA Code: 
    ListBox1.Clear
    For irow = 18 To 850
 
Upvote 0
Thanks. That was just me throwing it around to see where it was going wrong. It's always been placed above. I even created a separate buttong and cleared the list first, then ran the code and got the same error. What;s odd is that I've used this in other userforms with no issues. Example below. I just can't figure out what I did different here.

VBA Code: 
listCommentHistory.Clear
        
    For irow = 2 To 500
    
    If Trim(ws2.Range("A" & irow).Value) = True Then
        With listCommentHistory
  
    .ColumnCount = 4
    .ColumnWidths = "0;55;45;50"
    .AddItem
    .List(i, 0) = Trim(ws2.Range("A" & irow).Value)
    .List(i, 1) = Format(Trim(ws2.Range("E" & irow).Value), "dd/mm/yyyy")
    .List(i, 2) = Trim(ws2.Range("G" & irow).Value)
    .List(i, 3) = Trim(ws2.Range("H" & irow).Value)
        
    i = i + 1
            
        End With
    End If
Next irow
 
Upvote 0
If you are able, upload your workbook to the same place that I uploaded my example here.
Post back so I get notification.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
172
mikefisher
M
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
533
pure vito
P
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
166
Akuini
Akuini
S
  • Question
VBA Help - Need listbox to scroll to top when opened
Replies
1
Views
338
rlv01
R
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
343
pure vito
P

Forum statistics

Threads
1,215,336
Messages
6,124,330
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top