Arrays and ListBox help

[Al]

Hello folks (& Doug + Jonathon)

I have a form that prompts a user for personal information such as name and
address.

When completed the user hits the "ADD" button and the personal information
is store in a listbox (listbox2) that has 9 columns. (One column for each of
FirstName/Surname/Street/City etc....)

The listbox typically contains only a few people, and hence only a few rows
(one row per person).

The personal information is also store in an array.

Because Redim Preserve only works on the last element of an array the array
is transposed allowing the additon and deletion of records.

I need to code a DELETE button to delete records from the listbox2 and
subsequently the array.

Deleting records from the listbox2 is easy - the array is not so easy.

I have got my attempt at the code below but it comes up with a type mismatch
on the line;

OffenderArray = ListBox2.Column()

Anybody able to tell me how to finish this off? I think Doug Robbins sent me
the code to do it once but I lost it and cant find the post.

Cheers

-Al

Private Sub CommandButton2_Click()

' Macro to delete Defendants from Array

Dim NameCount
Dim NametoDelete
Dim Adjust_Array_Count
Dim i As Long

NameCount = ListBox2.ListCount ' Counts Number of Defendants
NametoDelete = ListBox2.ListIndex ' Identify currently select Defendant

' Procedure if no name selected
If NametoDelete = -1 Then
MsgBox "You must select a Defendant Name.", vbExclamation, "No Name
Selected"
Exit Sub
End If

' Procedure when name selected
ListBox2.RemoveItem NametoDelete 'Delete selected Defendant from Listbox
ReDim Preserve OffenderArray(9, ListBox2.ListCount - 1) 'Redefine Array

For i = 0 To ListBox2.ListCount - 1
MsgBox OffenderArray(0, i)
Next i

MsgBox "Stop", vbInformation, "Stop"

OffenderArray = ListBox2.Column()

For i = 0 To ListBox2.ListCount - 1
MsgBox OffenderArray(0, i)
Next i

MsgBox "Stop", vbInformation, "Stop"

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Al,

Use

OffenderArray = ListBox2.List()

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP