Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Pre-Select items in Multi-Select List on Form
Hi,
I have a worksheet that has 2 lists... buildList is a "master" list containing all possibilities for a field (like validation). currentList is active subset of the "master" list. Instead of modifying currentList manually, I want a form to pop up that shows a list of all the possibilies (buildList), but also pre-selects all the items in the currentList. My code below adds all the items properly, but I can't figure out how to pre-select items. I think the problem lies with this line of code: TheList1.List(i - 1).Selected = True This should get executed if the item currently being added to the list from the master list is contained in the "currentlist". It's possible that the index isn't right but am not sure. Thanks, MikeZz Private Sub UserForm_Initialize() Dim i, c, r, r1, c1 Dim AddThis Dim defR, defC Dim It errToMany = False TheList1.Clear tempCurr = currentList defR = UBound(currentList, 1) - LBound(currentList, 1) + 1 defC = UBound(currentList, 2) - LBound(currentList, 2) + 1 For i = LBound(buildList, 1) To UBound(buildList, 1) AddThis = True If (listType = "Company" And buildList(i) = CompanyName) Or _ buildList(i) = "Other" Or buildList(i) = "OT" Then AddThis = False End If If AddThis = True Then TheList1.AddItem buildList(i): c = TheList1.ListCount For r1 = 1 To defR For c1 = 1 To defC ' It = TheList1.List(TheList1.ListCount) If currentList(r1, c1) = buildList(i) Then ' TheList1.List(i - 1).Selected = True GoTo gotonextItem End If Next c1 Next r1 gotonextItem: End If Next i TheList1.AddItem "Other" Qty.Caption = 0 Qty2.Caption = TheList1.ListCount FormTitle.Caption = "Primary Co: " & CompanyName FormTitle.Visible = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Pre-Select items in Multi-Select List on Form
Make sure you have the option for your control set properly. The default is
xlNone see VBA help MultiSelect Property lb.ControlFormat.MultiSelect = xlSimple Remarks Single select (xlNone) allows only one item at a time to be selected. Clicking the mouse or pressing the SPACEBAR cancels the selection and selects the clicked item. Simple multiselect (xlSimple) toggles the selection on an item in the list when click it with the mouse or press the SPACEBAR when the focus is on the item. This mode is appropriate for pick lists, in which there are often multiple items selected. Extended multiselect (xlExtended) usually acts like a single-selection list box, so when you click an item, you cancel all other selections. When you hold down SHIFT while clicking the mouse or pressing an arrow key, you select items sequentially from the current item. When you hold down CTRL while clicking the mouse, you add single items to the list. This mode is appropriate when multiple items are allowed but not often used. You can use the Value or ListIndex property to return and set the selected item in a single-select list box. You cannot link multiselect list boxes by using the LinkedCell property. Example This example creates a simple multiselect list box. Set lb = Worksheets(1).Shapes.AddFormControl(xlListBox, _ Left:=10, Top:=10, Height:=100, Width:100) lb.ControlFormat.MultiSelect = xlSimple "MikeZz" wrote: Hi, I have a worksheet that has 2 lists... buildList is a "master" list containing all possibilities for a field (like validation). currentList is active subset of the "master" list. Instead of modifying currentList manually, I want a form to pop up that shows a list of all the possibilies (buildList), but also pre-selects all the items in the currentList. My code below adds all the items properly, but I can't figure out how to pre-select items. I think the problem lies with this line of code: TheList1.List(i - 1).Selected = True This should get executed if the item currently being added to the list from the master list is contained in the "currentlist". It's possible that the index isn't right but am not sure. Thanks, MikeZz Private Sub UserForm_Initialize() Dim i, c, r, r1, c1 Dim AddThis Dim defR, defC Dim It errToMany = False TheList1.Clear tempCurr = currentList defR = UBound(currentList, 1) - LBound(currentList, 1) + 1 defC = UBound(currentList, 2) - LBound(currentList, 2) + 1 For i = LBound(buildList, 1) To UBound(buildList, 1) AddThis = True If (listType = "Company" And buildList(i) = CompanyName) Or _ buildList(i) = "Other" Or buildList(i) = "OT" Then AddThis = False End If If AddThis = True Then TheList1.AddItem buildList(i): c = TheList1.ListCount For r1 = 1 To defR For c1 = 1 To defC ' It = TheList1.List(TheList1.ListCount) If currentList(r1, c1) = buildList(i) Then ' TheList1.List(i - 1).Selected = True GoTo gotonextItem End If Next c1 Next r1 gotonextItem: End If Next i TheList1.AddItem "Other" Qty.Caption = 0 Qty2.Caption = TheList1.ListCount FormTitle.Caption = "Primary Co: " & CompanyName FormTitle.Visible = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Pre-Select items in Multi-Select List on Form
I think I figured out the problem.
I set the code up to put "Other" at the end of the list just in case it fell in the middel on the master list. I had skipped "Other" until after other items were added but I didn't have a seperate counter of actual items added to the list. So when I skipped it, the list didn't grow (say only 3 long) but I tried to set the selected property as if it were added.... so an index issue. Thanks anyway.... it how works like a charm. Here's the finished code...... Private Sub UserForm_Initialize() Dim i, c, r, r1, c1, why Dim AddThis Dim defR, defC Dim It Dim items On Error GoTo ErrHandler init = True errToMany = False TheList1.Clear tempCurr = currentList defR = UBound(currentList, 1) - LBound(currentList, 1) + 1 defC = UBound(currentList, 2) - LBound(currentList, 2) + 1 items = 0 For i = LBound(buildList, 1) To UBound(buildList, 1) AddThis = True If (listType = "Company" And buildList(i) = CompanyName) Or _ buildList(i) = "Other" Or buildList(i) = "OT" Then AddThis = False End If If AddThis = True Then items = items + 1 TheList1.AddItem buildList(i): c = TheList1.ListCount If IsInVariantArray(buildList(i), currentList, True) Then TheList1.Selected(items - 1) = True GoTo gotonextItem End If gotonextItem: End If Next i TheList1.AddItem "Other": items = items + 1 If IsInVariantArray("Other", currentList, True) Then TheList1.Selected(items - 1) = True End If Qty.Caption = Selected_Count(TheList1) Qty2.Caption = TheList1.ListCount FormTitle.Caption = "Primary Co: " & CompanyName FormTitle.Visible = True init = False Exit Sub ErrHandler: why = MsgBox("Err Number: " & Err.Number & Chr(13) & _ "Err Number: " & Err.Number & Chr(13) & _ "Build List: " & buildList(i) & Chr(13), vbCritical) Resume Next End Sub "Joel" wrote: Make sure you have the option for your control set properly. The default is xlNone see VBA help MultiSelect Property lb.ControlFormat.MultiSelect = xlSimple Remarks Single select (xlNone) allows only one item at a time to be selected. Clicking the mouse or pressing the SPACEBAR cancels the selection and selects the clicked item. Simple multiselect (xlSimple) toggles the selection on an item in the list when click it with the mouse or press the SPACEBAR when the focus is on the item. This mode is appropriate for pick lists, in which there are often multiple items selected. Extended multiselect (xlExtended) usually acts like a single-selection list box, so when you click an item, you cancel all other selections. When you hold down SHIFT while clicking the mouse or pressing an arrow key, you select items sequentially from the current item. When you hold down CTRL while clicking the mouse, you add single items to the list. This mode is appropriate when multiple items are allowed but not often used. You can use the Value or ListIndex property to return and set the selected item in a single-select list box. You cannot link multiselect list boxes by using the LinkedCell property. Example This example creates a simple multiselect list box. Set lb = Worksheets(1).Shapes.AddFormControl(xlListBox, _ Left:=10, Top:=10, Height:=100, Width:100) lb.ControlFormat.MultiSelect = xlSimple "MikeZz" wrote: Hi, I have a worksheet that has 2 lists... buildList is a "master" list containing all possibilities for a field (like validation). currentList is active subset of the "master" list. Instead of modifying currentList manually, I want a form to pop up that shows a list of all the possibilies (buildList), but also pre-selects all the items in the currentList. My code below adds all the items properly, but I can't figure out how to pre-select items. I think the problem lies with this line of code: TheList1.List(i - 1).Selected = True This should get executed if the item currently being added to the list from the master list is contained in the "currentlist". It's possible that the index isn't right but am not sure. Thanks, MikeZz Private Sub UserForm_Initialize() Dim i, c, r, r1, c1 Dim AddThis Dim defR, defC Dim It errToMany = False TheList1.Clear tempCurr = currentList defR = UBound(currentList, 1) - LBound(currentList, 1) + 1 defC = UBound(currentList, 2) - LBound(currentList, 2) + 1 For i = LBound(buildList, 1) To UBound(buildList, 1) AddThis = True If (listType = "Company" And buildList(i) = CompanyName) Or _ buildList(i) = "Other" Or buildList(i) = "OT" Then AddThis = False End If If AddThis = True Then TheList1.AddItem buildList(i): c = TheList1.ListCount For r1 = 1 To defR For c1 = 1 To defC ' It = TheList1.List(TheList1.ListCount) If currentList(r1, c1) = buildList(i) Then ' TheList1.List(i - 1).Selected = True GoTo gotonextItem End If Next c1 Next r1 gotonextItem: End If Next i TheList1.AddItem "Other" Qty.Caption = 0 Qty2.Caption = TheList1.ListCount FormTitle.Caption = "Primary Co: " & CompanyName FormTitle.Visible = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
identifying multi-select items in a listbox | Excel Programming | |||
Multi-Select List Box | Excel Programming | |||
Hi-lighting items in a multi-select List box | Excel Programming | |||
Putting items from list box multi-select onto worksheet | Excel Programming | |||
Extract values from a multi-select multi-column list-box | Excel Programming |