View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Iain King Iain King is offline
external usenet poster
 
Posts: 32
Default Display selections from a listbox in a message box

Using the code below, I'm trying to display the selected items by a
user from listbox1 in a message box and have the user confirm their
selections using vbyes (send selection to Comboboxes on sheet2) or
vbno (do nothing & start again).

How can I go about doing this, I've tried setting up a message box to
pass in the selections, but cant seem to configure it properly.


Hi there - this should work. I've only used List and Combo boxes on a
Userform though, so if the ones you are using differ then this may not work.


Dim intIndex As Integer
Dim intComboIndex As Integer
Dim shtOne As Worksheet
Dim shtTwo As Worksheet
Dim Msg As String
Set shtOne = Worksheets("Step 1")
Set shtTwo = Worksheets("Step 2")

Msg = "Send this selection?"+chr$(13) 'chr$(13) is a carriage return

with shtOne.OLEObjects("ListBox1").Object
for intIndex = 0 to .ListCount - 1
if .Selected(intIndex) then
Msg = Msg + .List(intIndex) + chr$(13)
endif
next
end with

if msgbox( Msg, vbYesNo) = vbYes then 'send to combos
With shtOne.OLEObjects("ListBox1").Object
for intIndex=0 to .ListCount - 1
if .Selected(intIndex) then
intComboIndex = intComboIndex + 1
' sets the combobox to the value selected in the listbox
shtTwo.OLEObjects("ComboBox" & intComboIndex).Object.AddItem
..List(intIndex)
endif
next
End With
endif


One other point noting, is there a way that I can have the selections

sent to the Combobox on sheet 2 based on the order that they are selected.

Right now it only takes those that are selected based on the order that

they appear in the list.


Not easily. You would have to trap the click event on the Listbox, and
compare the new selection after a click with the selection it held
previously (which you would have to store seperately). That would tell you
what had been selected, which you could add to an array of your own. Howeve
r, handling deselections would complicate this - I wouldn't bother. Just
sort them into an arbitrary order and use that.

Iain King