Adding and Reading a ListBox Item

Hi,
I populate a 2 column, MultiSelectMulti, ListBox on a form with worksheet
names which have data like so:

If Sheets(i).Cells(rnum, cnum).Value > 0 Then
frmExport.lboSelectWeek.AddItem Sheets(i).Name
End If

The aim then is to select any number of sheets and copy paste the data to
another file. This I can manage ok. The problem arises when I try to
prevent a repeat copy paste of the same data.
To do this, after each sheet has been copied, a visual indicator is added to
the ListBox row and column of the sheet selected. The abbreviated code is:

For i = 0 To frmExport.lboSelectWeek.ListCount - 1

If frmExport.lboSelectWeek.Selected(i) = True Then
''*************

''< Do copy and paste to other workbook stuff >

frmExport.lboSelectWeek.List(i, 1) = "Done" 'put an
indicator in

End If

Next

I would now like to prevent a repeat copy paste of the same data - even
though there is an indicator 'done'. The reason is, I have a 'Select All'
checkbox.

I have tried to trap this possibility by substituting the line marked
************* with:

If frmExport.lboSelectWeek.Selected(i) = True And Not
frmExport.lboSelectWeek.List(i,1)= "Done" Then

but get an error.

Curiously, if I insert a message box to read the item like so:

MsgBox frmExport.lboSelectWeek.List(i, 0) & vbCr & _
frmExport.lboSelectWeek.List(i, 1)

The msgbox reads both items correctly. Where am I going wrong?

I would very much appreciate some advice.

Geoff

Its sorted now. Its amazing what a few minutes away from the scene can do
sometimes. I tested for Null and it worked. The line:

If frmExport.lboSelectWeek.Selected(i) = True _
And Not frmExport.lboSelectWeek.List(i,1)= "Done" Then

should be:

If frmExport.lboSelectWeek.Selected(i) = True _
And IsNull(frmExport.lboSelectWeek.List(i,1) Then

Still don't know why the msgbox read correctly but at least the main code
works now.

Geoff

"Geoff" wrote:

> Hi,
> I populate a 2 column, MultiSelectMulti, ListBox on a form with worksheet
> names which have data like so:
>
> If Sheets(i).Cells(rnum, cnum).Value > 0 Then
> frmExport.lboSelectWeek.AddItem Sheets(i).Name
> End If
>
> The aim then is to select any number of sheets and copy paste the data to
> another file. This I can manage ok. The problem arises when I try to
> prevent a repeat copy paste of the same data.
> To do this, after each sheet has been copied, a visual indicator is added to
> the ListBox row and column of the sheet selected. The abbreviated code is:
>
> For i = 0 To frmExport.lboSelectWeek.ListCount - 1
>
> If frmExport.lboSelectWeek.Selected(i) = True Then
> ''*************
>
> ''< Do copy and paste to other workbook stuff >
>
> frmExport.lboSelectWeek.List(i, 1) = "Done" 'put an
> indicator in
>
> End If
>
> Next
>
> I would now like to prevent a repeat copy paste of the same data - even
> though there is an indicator 'done'. The reason is, I have a 'Select All'
> checkbox.
>
> I have tried to trap this possibility by substituting the line marked
> ************* with:
>
> If frmExport.lboSelectWeek.Selected(i) = True And Not
> frmExport.lboSelectWeek.List(i,1)= "Done" Then
>
> but get an error.
>
> Curiously, if I insert a message box to read the item like so:
>
> MsgBox frmExport.lboSelectWeek.List(i, 0) & vbCr & _
> frmExport.lboSelectWeek.List(i, 1)
>
> The msgbox reads both items correctly. Where am I going wrong?
>
> I would very much appreciate some advice.
>
> Geoff