VBA EXCEL- Comparing listbox item to cell value (string)

Question

My list box is on a form. When a user selected list items they are stored in a column. Later when m edit button is clicked I would like to read in these values and add them onto a selected list (Listbox_selectedCategories) on the form and remove them from the other list box (Listbox_categories) The error that the VBA editor is throwing is error 381 could not get list property. Invalid property array index. I have checked both the value of x and index in the immediate window which are iterating as expected.I have also tried this without the Cstr function,same error. The compiler is highlighting the if statement as the point of error. Im sure this is some fundamental misunderstanding I would appreciate any help or direction. Thanks.

 Public Sub dataLoad()
 Dim x As Integer
 x = 0

NewQueryForm.targetingDescription.value = ActiveCell.Offset(1, 0).value
NewQueryForm.booleanDescription.value = ActiveCell.Offset(1, 1).value
NewQueryForm.startDate.value = ActiveCell.Offset(1, 3).value
NewQueryForm.endDate.value = ActiveCell.Offset(1, 4).value
NewQueryForm.dfpCount.value = ActiveCell.Offset(1, 5).value

NewQueryForm.Text_300Rates = ActiveCell.Offset(1, 8).value

NewQueryForm.Text_160Rates = ActiveCell.Offset(2, 8).value

NewQueryForm.Text_728Rates = ActiveCell.Offset(3, 8).value

NewQueryForm.Text_PollRates = ActiveCell.Offset(4, 8).value

NewQueryForm.Text_CMRates = ActiveCell.Offset(5, 8).value

NewQueryForm.Text_ExCMRates = ActiveCell.Offset(6, 8).value



Call NewQueryForm_Initialize

Sheets("CAT").Activate
Range("A1").Activate
ActiveCell.Offset(0, loopCount).Activate

While ActiveCell.Offset(1, 0).value <> ""
x = x + 1
Dim index As Integer

'Adding and removing items from list
NewQueryForm.ListBox_selectedCategories.AddItem ActiveCell.Offset(x - 1, 0).value

For index = 0 To NewQueryForm.ListBox_categories.ListCount - 1

If CStr(NewQueryForm.ListBox_categories.List(index)) = ActiveCell.Offset(x - 1, 0).value Then
            NewQueryForm.ListBox_categories.RemoveItem (index)
        End If

Next index

'Adding and Subtracting from global percent label variables
selectedCategoryLabel = selectedCategoryLabel + categoryPercent(ActiveCell.Offset(x - 1, 0).value)
categoryLabel = categoryLabel - categoryPercent(ActiveCell.Offset(x - 1, 0).value)

'Setting next cell down active
ActiveCell.Offset(1, 0).Activate
Wend

'updating labels
 NewQueryForm.selectedPercent.Caption = CStr(Round(selectedCategoryLabel, 2)) & "%"
 NewQueryForm.categoryPercent = CStr(Round(categoryLabel, 2)) & "%"





NewQueryForm.Show
End Sub

In brief you want to add items to the listbox if it's not already in the list box? Or do you want to remove if the values is in the list box? What are you trying to do please? — bonCodigo
– bonCodigo, Commented Jan 6, 2013 at 19:41
In brief I have a column of strings. I would like to add the value to one list box and remove the same value from the other list box. — drD
– drD, Commented Jan 6, 2013 at 19:47
In your query you only have one listbox and can you provide the entire subroutine please? coz there are no declarations on x and this activeCell events. What's the event you are triggering this? Are you using a button click? — bonCodigo
– bonCodigo, Commented Jan 6, 2013 at 19:55
ok so first a user fills out a form and selects categories, these categories are being stored on a sheet later an edit button is being clicked on- values from a data set are being placed into a form, the form has two list boxes (listbox_categories)-where I want to remove the activecell value from and listbox_selectedcategories where I am adding the activecell value (replicating their selected categories) — drD
– drD, Commented Jan 6, 2013 at 20:00
I have given you some tips how you can also add items into list from an Array at once, instead of one by one. Run the code and please comment. You will see where the error is ;) you just need to add two word line to your code... :) — bonCodigo
– bonCodigo, Commented Jan 6, 2013 at 20:46

bonCodigo · Accepted Answer · 2013-01-06 20:50:57Z

4

Following is the code I tried. I got the exact same error. And here is how I solved it.

BINGO: EXIT THE LOOP :)

Option Explicit    
'--show form on Sheet with Reg Edit mode
 Private Sub CommandButton1_Click()
    UserForm1.Show vbModeless
 End Sub   

'--Form codes within Form

Option Explicit

Private Sub cmdAddRange_Click()
Dim ws As Worksheet
Dim rng As range
Dim inputArray As Variant
Dim lRow As Long
Dim index As Integer

Set ws = Sheets(1)
Set rng = ws.range("B2")
lRow = ws.range("B" & ws.Rows.Count).End(xlUp).Row
inputArray = Application.WorksheetFunction.Transpose(rng.Resize(lRow).Value)

'Adding item to List one by one from Array
'For index = LBound(inputArray) To UBound(inputArray)
'UserForm1.ListBox1.AddItem inputArray(i)
'Next i

'Adding items to List box at ones from Array
UserForm1.ListBox1.List = inputArray
UserForm1.ListBox2.List = inputArray

End Sub

Private Sub cmdRemoveRange_Click()
Dim ws As Worksheet
Dim index As Integer

    Set ws = Sheets(1)

    '-- Remove  from listbox1
    For index = 0 To UserForm1.ListBox1.ListCount - 1

        If Not IsEmpty(ActiveCell.Value) Then
            If UserForm1.ListBox1.List(index) = ActiveCell.Value Then
                UserForm1.ListBox1.RemoveItem (index)
                MsgBox ActiveCell.Value & " deleted. And Golden Line *Exit For*"
                Exit For '-- golden line
            End If
        End If
    Next index

End Sub

Here is the Form I tried out:

enter image description here

edited Jan 6, 2013 at 20:50

answered Jan 6, 2013 at 20:44

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bonCodigo Over a year ago

@Remou I removed! And gave up sigh :(

Collectives™ on Stack Overflow

VBA EXCEL- Comparing listbox item to cell value (string)

1 Answer 1

BINGO: EXIT THE LOOP :)

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

BINGO: EXIT THE LOOP :)

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related