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

Adding and Reading a ListBox Item
 

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