Display selections from a listbox in a message box

[Excel-erate2004]

Hello to all,


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

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


Code
-------------------

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")


For intIndex = 0 To shtOne.OLEObjects("ListBox1").Object.ListCount - 1
If shtOne.OLEObjects("ListBox1").Object.Selected(intIndex) Then
intComboIndex = intComboIndex + 1


With shtTwo.OLEObjects("ComboBox" & intComboIndex).Object
'.Clear

' sets the combobox to the value selected in the listbox
.Value = shtOne.OLEObjects("ListBox1").Object.List(intIndex)
End With

End If
Next
[\code]

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.

Thanks for any help I can get

 

[Iain King]

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

 

[Excel-erate2004]

Thanks for the help Iain, I had to make a slight modification on thi
line:


Code
-------------------

shtTwo.OLEObjects("ComboBox" & intComboIndex).Object.AddItem
..List(intIndex)

-------------------



and replace it with this:


Code
-------------------

With shtTwo.OLEObjects("ComboBox" & intComboIndex).Object

' sets the combobox to the value selected in the listbox
.Value = shtOne.OLEObjects("ListBox1").Object.List(intIndex)
End With


-------------------



It doesn't like the additem.List(IntIndex) property for some reason.
get a run time error saying permission denied??

But with that slight modification it works great! Thanks a bunch!

I may just leave the other issue alone for now, its not terribl
important.

Thanks again