Solved: Removing a item from a listbox

[Rob342]

Hi All
I have a single select listbox, which I am trying to remove the selected item, But keep getting an undefined error, I have since found out that you cannot remove an item if the rowsource is used
The range used is a defined range called SRErrCode
This is the code that I had to load the listbox

Case "Service Reception"
With Me.LBAuditP4
.RowSource = "AC!V2:AD8"
.ColumnCount = 9
.ColumnHeads = True
.ColumnWidths = "30pt;500pt;30pt;40pt;40pt;40pt;40pt;40pt;40pt"
End With

This is what I changed it to

Case "Service Reception"
With Me.LBAuditP4
For i = 1 To 6
.AddItem ws.Range("SRErrCode").cells(.ListIndex + 2, i).Value
Next i


The 2nd piece of code only loads The 1st cell, how can I get it to load all 9 cells ( col V2 to V8) on each line of the listbox

I can remove the item selected when the above code is used
Is there a better way to load the listbox?
Any help would be most appreciated

Rob

[SamT]

Assumes that SRErrCode is Range("V2:V8") and no other
Case "Service Reception"
With Me.LBAuditP4
For i = 1 To Range("SRErrCode").Rows.Count
.AddItem ws.Range("SRErrCode").Cells(i).Text
Next i
If SRErrCode has several Columns you want to use:.AddItem ws.Range("SRErrCode").Cells(i, DesiredCol).Text
'Where DesiredCol is the number of the Column within the range itself, regardless of the sheet Column number.

[snb]

I'd prefer



Case "Service Reception"
With LBAuditP4
.List = sheets("AC").range("V2:AD8").value
.ColumnCount = ubound(.list,2)+1
.ColumnWidths = "30pt;500pt;30pt;40pt;40pt;40pt;40pt;40pt;4pt"
End With


To remove a listitem:


LBAuditP4.removeitem LBauditP4.listindex

[Rob342]

Thanks Sam and snb
1 Last question, when the listbox is empty what would be the best routine for checking this ie
With Me.LBAuditP4
IF IsEmpty then ..........
'Do something
End with

Am I on the right track

Rob

[snb]

If LBAuditP4.Listcount=0 Then

[Rob342]

Thanks both for you help most appreciated