Excel form - listbox

[Mr GT]

Hi!

(sorry for my english)

On the form, I have a button to add files to a listbox (the box is bound to a worksheet)

Someone know how to program a button to remove a line from the listbox and the worksheet?

[vba]
Private Sub BoutonAjoutFichiers_Click()
Dim fichiers As String
FormulaireBackup.CommonDialog1.CancelError = True
On Error GoTo Annulé
FormulaireBackup.CommonDialog1.ShowOpen
fichiers = FormulaireBackup.CommonDialog1.Filename
Worksheets("ListeFichiers").Cells(Range("NoligneFile").Value + 1, 1) = fichiers
BoxListeFichiers.RowSource = "ListeFichiers!A2:A" & Trim(Range("NoligneFile").Value + 1)
Range("NoligneFile").Value = Range("NoligneFile").Value + 1
Annulé:
Exit Sub

End Sub
[/vba]

Thanks!

[Bob Phillips]

If it is bound to the worksheet just delete a line in the worksheet, the listbox will reflect this.

[Mr GT]

If it is bound to the worksheet just delete a line in the worksheet, the listbox will reflect this.

Hi!

Yes I know, but I want to be able to click an item in the listbox to select it then click the delete button.

I need the code for this delete button. I have no clues on how to program this.

The user won't have access to the worksheet. The user will only see the form.

[Bob Phillips]

So just find the selected item in the worksheet and delete it. Look at FIND in VBA help.

[Mr GT]

My solution :

[vba]
Private Sub BoutonEnleveFichier_Click()
Dim sEffacerFichier As String
Dim R As Range
Dim FindAddress As String

On Error GoTo ChoisirFichier 'Erreur si un utilisateur ne choisit pas de fichier.

sEffacerFichier = BoxListeFichiers.Value

With Worksheets("ListeFichiers").Range("A1:A1048576") 'Spécifie la plage où chercher
Set R = .Find(sEffacerFichier) 'Cherche la première occurence
If Not R Is Nothing Then 'Si une correspondence est trouvé alors
FindAddress = R.Row 'Mettre dans une variable l'adresse de la cellule ou l'occurence est trouvé
Rows(FindAddress).Select
Selection.Delete Shift:=xlUp
End If
End With

Set R = Nothing 'Effacer la mémoire
Range("NoligneFile").Value = Range("NoligneFile").Value - 1
Exit Sub

ChoisirFichier:
MsgBox "SVP, sélectionner un fichier.", vbOKOnly 'Oblige l'utilisateur à spécifier un fichier

End Sub
[/vba]

[Bob Phillips]

That looks good to me, so well done, hopefully you enjoyed doing that yourself.

Note how I added [vba] and [/vba] around your code, it makes it more readable.

Just one thing you can improve upon. This code

[vba]

Rows(FindAddress).Select
Selection.Delete Shift:=xlUp
[/vba]

doesn't need the select, you can do

[vba]

Rows(FindAddress).Delete Shift:=xlUp
[/vba]

I am esepecially impressed that you delete the by the row property, that shows real understanding IMO

[mdmackillop]

BTW
rather than Range("A1:A1048576")
I would use
Worksheets("ListeFichiers").Range("A:A")
or
Worksheets("ListeFichiers").Columns(1)
for the entire column. These methods also give compatability with earlier versions of Excel which did not have that number of rows.

You may also see this method used for the same reason.
Range("A10:A" & Rows.Count)