Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display selections from a listbox in a message box
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(intI ndex) 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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display selections from a listbox in a message box
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display all pivottable filter selections | Excel Worksheet Functions | |||
Creating Chart from Userform ListBox selections | Charts and Charting in Excel | |||
Want to get user selections from a message box using option buttons | Excel Discussion (Misc queries) | |||
Jumping to chart based on listbox selections | Excel Programming | |||
changing foreground color of listbox w/o losing selections | Excel Programming |