How to use a ListBox & Delete Row

[grady121]

I've not used a ListBox before and would like some help on how it works.

I already have the range (see below) to capture the list of Names on Sheets(1) that I would like to populate in the ListBox1.

Application.Goto Cells(Rows.Count, "A").End(xlUp)
Range(Selection, Range("A10")).Select


What I need now is help with a code to populate the ListBox and also if possible, a code that when the user selects a Name, deletes the corresponding row from the worksheet.

Any help appreciated

 

[Tom Urtis]

Is this a ListBox that is on a userform or embedded onto a worksheet

and

if it is embedded onto a worksheet then

is it created from the Forms toolbar or the activex control toolbox

and

what is the name of the worksheet

and also

what is in column A *other than* (if anything) the list of names you speak of

and

are those names in those cells because they were entered as constants, or are they there because really there are formulas in those cells returning the names.

 

[grady121]

Thanks for the reply
Sorry for the confusion.

The Listbox is placed on a Userform via the VB Editor, with a CommandButton1 (OK button) placed below the Listbox to delete the selected/highlighted row.

The lists of names are only entered as Text in column A, but may have a variable amount of names within the range.

The actual worksheet will also have a variable name (Month & Year) but the list of names will always be taken from Sheet 1

Hope this helps

 

[Tom Urtis]

Assuming your list starts in cell A1 (that is, row 1 of Sheet1) then in your userform module, which all worked for me when I just tested it now...

Paste in this Initialize event:

Private Sub UserForm_Initialize()
With Sheet1.Range("A1")
ListBox1.List = .Resize(.CurrentRegion.Rows.Count, 1).Value
End With
End Sub

and paste in this Click event for your CommandButton1:

Private Sub CommandButton1_Click()
With ListBox1
If .ListIndex = -1 Then
MsgBox "You did not select anything in the lesit box.", 48, "Cannot continue."
Exit Sub
End If
Dim i&, xRow&, strSelected$
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
xRow = i + 1
Exit For
End If
Next i
strSelected = .List(.ListIndex)
'a little safer using Find than xRow = i
xRow = Sheets("Sheet1").Columns(1).Find(What:=strSelected, LookIn:=xlFormulas, LookAt:=xlWhole).Row
Dim myConf%
myConf = MsgBox("You selected " & strSelected & " on row " & xRow & "." & vbCrLf & _
"Do you want to delete that row?", 36, "Please confirm")
If myConf = 7 Then
MsgBox "No problem, nothing will be deleted.", 64, "You clicked No."
Exit Sub
End If
End With
With Sheets("Sheet1")
.Rows(xRow).Delete
ListBox1.Clear
ListBox1.List = .Range("A1").Resize(.Range("A1").CurrentRegion.Rows.Count, 1).Value
End With
MsgBox xRow & " has been deleted from Sheet1.", 64, "Done"
End Sub

 

[grady121]

Thanks for the comprehensive response Tom

It works exactly as I would have hoped, including the MsgBox's.

Superb response and more than I hoped for.