Listboxes - automatically unselecting themselves?!

[sp00n]

Hi all,
I've been teaching myself vba for the past week, and up until now all has seemed to be going well.

For some reason today when my macros run the selections that were made to multi-select listboxes seem to disappear.

To put it in some context, I have two multi-select listboxes each has a range to populate it. When a user changes their selection the worksheet automatically updates itself filtering on your selection.
In some instances data on the sheet is cut and pasted to dynamically add a number of columns into the sheet.

I have tracked it down to find it removes selections when the data seems to be being cut and pasted etc, though have no idea why it would do that?!

Can anyone lend some advice?

Thanks

[Bob Phillips]

Not without seeing the code.

[sp00n]

Thanks for the reply,
here's some of the code:

'If not all of the products from the list have been selected then...
If Not bAllProducts Then
Dim nProdCell As Integer

'Find last column and row of data
LastCol = DispSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
LastRow = DispSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

'Find first column we want to search from
FirstCol = DispSheet.Cells.Find(What:="Rank", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
FirstCol = FirstCol + 1
'Get the range
sRange = ColumnLetter(FirstCol) & nHeaderRow & ":" & ColumnLetter(LastCol) & nHeaderRow

'Find Totals for each selected Account in our selection
DispSheet.Range(sRange).Select
Set R = Selection.Find(What:="Total ARB MKT", SearchDirection:=xlNext, SearchOrder:=xlByColumns)

If Not R Is Nothing Then
FindAddress = R.Address
Do

LastCol = DispSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
'Cut and paste data to enable new column to be added
DispSheet.Columns(ColumnLetter(R.Column + 1) & ":" & ColumnLetter(LastCol)).Select
Selection.Cut
DispSheet.Columns(ColumnLetter(R.Column + 2) & ":" & ColumnLetter(LastCol + 1)).Select
ActiveSheet.Paste
DispSheet.Cells(nHeaderRow, R.Column + 1).Value = "Total ARB MKT - Selected Products"

'Loop through the dataset updating the Selected Accs ARB MKT
For i = nFirstRow To LastRow
DispSheet.Range(ColumnLetter(R.Column + 1) & nHeaderRow & ":" & ColumnLetter(LastCol) & nHeaderRow).Select
'Loop through the Acc products to get total
For p = 0 To DispSheet.OLEObjects("ProdListBox").Object.ListCount - 1

If DispSheet.OLEObjects("ProdListBox").Object.Selected(p) And Not IsEmpty(DispSheet.OLEObjects("ProdListBox").Object.List(p)) Then
Set Pr = Selection.Find(What:=DispSheet.OLEObjects("ProdListBox").Object.List(p), SearchDirection:=xlNext, SearchOrder:=xlByColumns, lookat:=xlWhole)
DispSheet.Cells(i, R.Column + 1).Value = DispSheet.Cells(i, R.Column + 1).Value + DispSheet.Cells(i, Pr.Column).Value
End If
Next

Next
sRange = ColumnLetter(R.Column + 1) & nHeaderRow & ":" & ColumnLetter(LastCol) & nHeaderRow
DispSheet.Range(sRange).Select
'Search for the next cell with a matching value
Set R = Selection.Find(What:="Total ARB MKT", SearchDirection:=xlNext, SearchOrder:=xlByColumns)
If R Is Nothing Then Exit Do
Loop While R.Address <> FindAddress
End If
....and on it goes.

It breaks in the do..while loop where it seems to be have somehow deselected all the selected products then goes off on one creating loads and loads of columns

[malik641]

Tough to say. If you are modifying the cells that your RowSource points to then you may want to look into that. When you have a listbox with the RowSource set, anytime you modify the cells it will automatically reflect on the listbox. And I see a lot of cutting and pasting in your code. It's kind of weird, though, because you say it's deselecting and not removing or clearing the values in the listbox...

Is there a way you can post the workbook?

[mdmackillop]

You can post a workbook using Manage Attachments in the Go Advanced reply section