Loop through Listbox and select item if found

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,317
Office Version
  1. 2016
Platform
  1. Windows
I have a multi-select Listbox on a userform and I am trying to select an item using VBA if the value of a textbox is specific.

This is what I have but I keep getting an error that the control source could not be set;

VBA Code:
For i = 1 To 12

If CheckFrm("TextA" & i).Value = "Issues found" Or CheckFrm("TextA" & i).Value = "Not checked" Or CheckFrm("TextA" & i).Value = "Out of service" Then
ListBoxCells.Selected("A" & i) = True
End If

For info, the Listbox is populated when the userform loads like this;

VBA Code:
With ListBoxCells

For i = 1 To 12
.AddItem "A" & i
Next i

For i = 13 To 24
.AddItem "B" & i
Next i

For i = 25 To 36
.AddItem "C" & i
Next i


End With

Can someone please put me out of my misery?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,317
Office Version
  1. 2016
Platform
  1. Windows
I have found what I thought was a solution but the problem is that it's adding each found item twice to the next Listbox if anyone can assist?

VBA Code:
For x = 0 To ListBoxCells.ListCount - 1
If ListBoxCells.Selected(x) = True Then
With ListBoxCells2
.AddItem ListBoxCells.List(x)
End With
End If
Next x

For x = 0 To ListBoxCells.ListCount - 1
If ListBoxCells.Selected(x) = False Then
With ListBoxCells3
.AddItem ListBoxCells.List(x)
End With
End If
Next x
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
The selected property of a Listbox control has the appearance (in memory) of an one dimensional array. The members of this array can only be accessed by means of a numeric index value. Since ("A" & i) is by definition alphanumeric due to the letter A, this will result in an error.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
From your code alone I cannot determine exactly what you want to achieve.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,441
Members
410,684
Latest member
LakTik
Top