PDA

View Full Version : Multi-Delete Function Problem in ListBox1



Ann_BBO
07-20-2007, 08:05 AM
Hi ~ ALL

This vba can Open the Files with multiselect function and the files path will show in the listbox. Also, it can delete the single file path in list box.

? Multi-select the file(s) and list in the listbox1
Private Sub cmdInput_Click()
Dim Files As Variant
Dim sFileShort As String
Dim i As Long
Dim Title As String
Dim Finfo As String
Dim Msg As String

' Setup lists of file filters
Finfo = "Exceld Files (*.xls),*.xls,"


' Set the dialog box caption
Title = "select a File to Import"
Files = Application.GetOpenFilename(Finfo, , Title, MultiSelect:=True)

' Exit if dialog box canceled
If Not IsArray(Files) Then
MsgBox "No file was selected."
Exit Sub
End If

'Display full path and name of the files
For i = LBound(Files) To UBound(Files)
FileShort = Right(Files(i), Len(Files(i)))
With Me.ListBox1
.AddItem sFileShort
.List(.ListCount - 1, 1) = Files(i)
End With
Next i
End Sub

' Delete the single file
Private Sub cmdDelete_Click()
Me.ListBox1.RemoveItem (Me.ListBox1.ListIndex)
End Sub

Then, i try to modify it with the Multi-Delete function. Although it can multi-select the files in listbox, the action stills delete single file when i click the delete button.

With Me.ListBox1
.MultiSelect = 2
.AddItem sFileShort
.List(.ListCount - 1, 1) = Files(i)
End With

Therefore, how to modify it with multi-delete function. Thanks

Bob Phillips
07-20-2007, 08:28 AM
Private Sub cmdDeelete_Click()
Dim i As Long
With Me.ListBox1
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) Then
.RemoveItem i
End If
Next i
End With
End Sub

Ann_BBO
07-20-2007, 09:15 AM
According the lists, When i select the bottom of list of files, it deletes all the files in listbox. Also. it cannot delete 2 or above files in once.

Thanks

Bob Phillips
07-20-2007, 09:28 AM
Pardon?

Ann_BBO
07-20-2007, 09:56 AM
With your suggested VBA command, it can't multi-select the files in listbox. Also, it will delete all files in listbox when i selected the bottom file in listbox. I want to delete 2 or above files when i click "delete button" once(multi-delete). Delete the number of files which depend on the user like. I want to multi-select files in listbox which can be multi-deleted the selected files.

Bob Phillips
07-20-2007, 10:02 AM
No it doesn't, it deletes whatever items in the listbox that you have selected, as you were showing (albeit for a singleton item) in your example.

daniel_d_n_r
07-20-2007, 03:02 PM
if you use multiselect (i used check boxes)
this code will delete all the selcted/checked items in the listbox
all at once without prompt
you may have to add a handler for nothing selected.

might be some help, I am not too sure

i would definitely add a single prompt at the end to ensure your not removing needed sheets

cheers

---------------------------------------------
For i = 0 To ListBox1.ListCount
While ListBox1.Selected(i) = True
Application.DisplayAlerts = False
ThisWorkbook.Sheets.Select (i)
ThisWorkbook.Sheets(i).Delete
UserForm1.ListBox1.RemoveItem (i)
Wend
next

Ann_BBO
07-20-2007, 05:01 PM
It means that it can't multi-select the selected items in listbox which can multi-delete the selected items in my case.

If refer to daniel suggestion, how to add a single prompt in the case.
Thanks

daniel_d_n_r
07-20-2007, 05:39 PM
not sure, its up to you really,
maybe just a msgbox displaying the items to be removed,
just to be safe, so the user can check the selection again before final removal

Ann_BBO
07-20-2007, 06:32 PM
Hello all !!!
I can do that the purpose of multi-select the selected items in listbox which can multi-delete the selected items . Or I am very tired in last night. Xld suggest the vba command is works and plus my modified vba command.Here it is:
'Display full path and name of the files
For i = LBound(sFiles) To UBound(sFiles)
sFileShort = Right(sFiles(i), Len(sFiles(i)))
With Me.ListBox1
.MultiSelect = 2
.AddItem sFileShort
.List(.ListCount - 1, 1) = sFiles(i)
End With
Next i

Private Sub cmdDelete_Click()
Dim k As Long
With Me.ListBox1
For k = .ListCount - 1 To 0 Step -1
If .Selected(k) Then
.RemoveItem k
End If
Next k
End With
End Sub

Anyway thanks xld and daniel

Ann_BBO
07-22-2007, 06:34 PM
How to modify the below vba that can all open the filelist in the listbox at once?
Private Sub cmdRun_Click()
Workbooks.Open Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex)
End Sub

Thanks

Bob Phillips
07-23-2007, 12:27 AM
Private Sub cmdRun_Click()
Dim i As Long
With Me.ListBox1
For i = 0 To .ListCount
Workbooks.Open Filename:=.List(i)
Next i
End With
End Sub