Listbox Selection

[mdo8105]

Hi,
I'm attempting to prioritize what is selected first in List box for example; I have source that has 1,2,3,4 The user will select 2,1,and 3. When the output comes out into a cell, I want it to show 2,1,3 not 1,2,3. Here is the code that I have; however, I'm getting a subscript Out of Range on

If listSelect(i) = totalSelect Then

Here is my Code at the top of the Userform:

Option Explicit 
Dim listSelect() As Integer, totalSelect As Integer, eventsEnabler As Integer
Private Sub Userform_Intialize()
' Disable DEPListBox_Change() code
eventsEnabler = 1
 
With DEPListBox
     
     ' .AddItems here
     
End With
 
ReDim listSelect(0 To DEPListBox.ListCount - 1)
totalSelect = 0
 
 ' Enable _Change() code
eventsEnabler = eventsEnabler - 1
End Sub

Then I have a Change in as well:

Private Sub DEPListBox_Change()Dim i As Integer, j As Integer, indexStart As Integer, indexEnd As Integer, stepDir As Integer
     
     ' When setting up DEPListBox, set eventsEnabler to > 0 to prevent this code from executing!
    If eventsEnabler = 0 Then
         
         ' Start by assuming we'll search for new selections top-to-bottom
        indexStart = 0
        indexEnd = DEPListBox.ListCount - 1
         
         '       This block determines direction of additional selections (up or down)
        For i = 0 To DEPListBox.ListCount - 1
             ' If we've hit the previous last selection, we already know the answer (top-to-bottom)
            If listSelect(i) = totalSelect Then Exit For
             ' If we hit a new selection before finding the previous last selection,
             ' then we'll change the search for new selections to be bottom-to-top
            If listSelect(i) = 0 And DEPListBox.Selected(i) Then
                indexStart = indexEnd
                indexEnd = 0
                Exit For
            End If
        Next i
         
        stepDir = Sgn(indexEnd - indexStart)
        If stepDir = 0 Then stepDir = 1
         
         '       Update selection list in listSelect()
        For i = indexStart To indexEnd Step stepDir
            If DEPListBox.Selected(i) = True Then
                 '               Newly selected item: place selection number in listSelect() and update totalSelect
                If listSelect(i) = 0 Then
                    listSelect(i) = totalSelect + 1
                    totalSelect = totalSelect + 1
                End If
            Else
                 '               Deselected item: remove its selection number, and update all others to compensate.
                If listSelect(i) > 0 Then
                    For j = 0 To DEPListBox.ListCount - 1
                        If listSelect(j) > listSelect(i) Then listSelect(j) = listSelect(j) - 1
                    Next j
                    listSelect(i) = 0
                    totalSelect = totalSelect - 1
                End If
            End If
        Next i
         
         ' At this point, totalSelect and listSelect() are updated for you to act upon.
        
    End If
End Sub

 

[Norie]

You seem to have a lot of code in your change event, what's it all meant to do.

P.S. Is it a single select listbox?

 

[mdo8105]

Hi Norie,

It is a multiselectExteneded and my attempt with the code is to set the order in which the user selects the values; therefore, whichever they click first would be the first in the list of outputs. Essentially the list is a dynamic range of Clinics and the user has to choose their primary clinic first and then choose the rest of the clinics they are associated with.

Thank you,

 

[Norie]

Wouldn't it be easier to use a single select listbox?

Each time they make a selection that selection is added to a dynamic array.

Or how about using a second listbox that's populated with their selection(s) from the DEPListBox.

 

[mdo8105]

Would I still be able to get the complete output in one cell in Char(10) format?

 

[Norie]

Yes, you would populate the cell with the data from the dynamic array.

Something like this.

Option Explicit
Dim listSelect()
Dim cnt As Long

Private Sub CommandButton1_Click()
    Range("A1").Value = Join(listSelect, vbLf)
End Sub

Private Sub Userform_Initialize()
 
    DEPListBox.List = Array("DEP1", "DEP2", "DEP3", "DEP4", "DEP5")
    
End Sub

Private Sub DEPListBox_Change()

    If DEPListBox.ListIndex <> -1 Then
        cnt = cnt + 1
        ReDim Preserve listSelect(1 To cnt)
        listSelect(cnt) = DEPListBox.List(DEPListBox.ListIndex)
        LabelDEPSelected.Caption = Join(listSelect, ",")
    End If
    
End Sub

Note this uses a label, LabelDEPSelected, to display what the user has selected so far but that's not strictly needed.

 

[mdo8105]

Thank you so much Norie,

I took your concept to fit into what I am doing and I can get it to work on the Combobox; however, not on the ListBox. Here is what I have set up on the Listbox that is collecting all of the chosen Values:

Private Sub MDepList_Click()selectAllDeptLists MDepList
End Sub

Here is my function for selectAllDeptLists and MDepList is the Listbox name

Function selectAllDeptLists(listBox)

    '''''''''''''''''''''''''''''''''''''
    ' Highlights index in all listboxes '
    '''''''''''''''''''''''''''''''''''''


    Dim a As Integer
    
    For a = 0 To listBox.ListCount - 1
        If listBox.Selected(a) Then
            MDepList.Selected(a) = True
            
        End If
    Next a


End Function

Then this is my first command button to add first chose from combo Box PDepCombo by user (which this works):

Private Sub CMDPDEP_Click()If PDepCombo <> "" Then


        addToList MDepList, PDepCombo, False
End If


End Sub

Here is my code for function addToList:

Function addToList(listBox, combobox, rmCBVal As Boolean)

    'Add new item to listbox
    listBox.AddItem combobox.Value
    
    'Remove new item from combobox
    If rmCBVal Then
        combobox.RemoveItem combobox.ListIndex
    End If
    'Reset combobox value to null
    combobox.Value = ""


End Function

Here is my final Command Button that adds the values a user select from a Listbox, DEPListBox is multiselect.

Private Sub CmdAddClinc_Click()If DEPListBox <> "" Then


        addToList MDepList, DEPListBox, False
End If
End Sub

Thank you,

 

[Norie]

Is the listbox multiselect or single select?

PS Where did you use what I suggested?

 

[mdo8105]

The listbox the user is choosing is Multi select, the listbox where the data is ending up in is Single select. I used more of your idea then the actual code. I'm somewhat of a novice with VB and did not understand this part:

[COLOR=#333333]DEPListBox.List = Array("DEP1", "DEP2", "DEP3", "DEP4", "DEP5")[/COLOR]

The scenario I have is there is a set of two questions I am asking a user. There first question What is their primary clinic they see patients in? (This is a ComboBox that is pulling from dynamic range) The second question is What are the other Clinics they see patients in (This is a Multiselect Listbox that is pulling from the same Dynamic Range)

Thank you so much for your patience with me.

 

[Norie]

I think you might be over complicating things.

If you have 2 listboxes why not make them both single select and use transfer items from one to the other one at a time, perhaps by using a double click event.

i.e. they double click an item in listbox1 and it gets added to listbox2 and removed from listbox1, and vice versa, they double click an item in listbox2 and it's moved to listbox1

That way the items will appear in listbox2 in the order they double clicked them.

P.S. All this code does is populate the listbox DEPListBox.

DEPListBox.List = Array("DEP1", "DEP2", "DEP3", "DEP4", "DEP5")