userform listbox refresh when writing to sheet (I don't want it to)

[IknowMe]

I have a listbox that is populated via rows on a worksheet. Currently it's set up to allow multiple selections. Once the user has made their selections I want to loop through the listbox and for each .selected = true I write something on the coresponding row of the worksheet.

I have the bulk of this done with one issue remaining. When I hit the first .selected I write successfully to the sheet but it then is clearing my remaining listbox selections if any. I'd like the selections to remain selected until after I complete the loop. Any Ideas?

Currently working around it with an additional loop that is being populated with the indexs of selected items then looping again to write to the worksheet. I'd prefer to bring the three loops down to one.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[combo]

You can temporarily lock list contents by replacing reference by list. I used modeless userform with two buttons. This code does not preserve selection.

Dim strListRef As String
Dim v

Private Sub CommandButton1_Click()
' "freeze" list
With Me.ListBox1
    strListRef = .RowSource
    v = .List
    .RowSource = ""
    .List = v
End With
End Sub

Private Sub CommandButton2_Click()
' relink list
Me.ListBox1.RowSource = strListRef
End Sub

Maybe it would be easier to link the list with worksheet only when the list needs to be refreshed or populate it purely by code (v=objRangebjList.List=v).

combo

 

[IknowMe]

Thanks for the info combo. I haven't had a chance to try it out yet, but I will soon.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]