Respective Selected Items to be displayed in Listbox as per the current record

**pike** · Jul 23rd 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

try this one with out the toggle button

SubirMehta · Jul 23rd 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

pike

Thanks for making it easier.

I get Run time Error '5' Invalid procedure call or argument
i.e in Public Sub Text_Current_Display(cur As Long)
......ReDim NewArray(1 To mylist(cur).count, 1 To 3)
i just pressed Next without adding.
also it can happen that there will be items(Board$) with 0 Schools or Records. How can we make it smooth without run time error.

Secondly what about the result in Sheet4 which i desired to display all the Added Selected items.

Regards

Subir

**pike** · Jul 23rd 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

afternoon .. replace all the code in the userform with

Code

Option Explicit
Dim mylist As New Collection
Private Sub cmdSelectAdd_Click()
    Dim myItem As Collection
    Dim iCount, blnSelected As Boolean
    blnSelected = False
    Label2.Caption = Label2.Caption + 1
    Set myItem = New Collection
    For iCount = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(iCount) Then
            blnSelected = True
            myItem.Add Array(ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2))
        End If
    Next iCount
    If blnSelected Then
        mylist.Add myItem
    Else
        Label2.Caption = Label2.Caption - 1
    End If
End Sub
Private Sub CmdNext_Click()
    If Not Label4.Caption = Label2.Caption Then
        Label4.Caption = Label4.Caption + 1
        Text_Current_Display (Label4.Caption)
    End If
End Sub
Private Sub cmdPrevious_Click()
    If Not Label4.Caption <= 1 Then
        Label4.Caption = Label4.Caption - 1
        Text_Current_Display (Label4.Caption)
    End If
End Sub
Private Sub ComboBox1_Change()
    TextBox1.Text = ComboBox1.Text
End Sub
Private Sub CommandButton1_Click()
    TextBox1.Text = vbNullString
    ComboBox1.Value = vbNullString
    TextBox1_Change
End Sub
Private Sub TextBox1_Change()
    Dim i As Long, intRow As Long
    Dim Str As String, rngSource


    Str = TextBox1.Text
    With Sheet1
        intRow = .Cells(Rows.count, 1).End(xlUp).row
        rngSource = .Range("A2:C" & intRow).Value
    End With
    If Not Str = "" Then
        For i = ListBox1.ListCount - 1 To 0 Step -1
            If InStr(1, LCase(ListBox1.list(i, 0)), LCase(Str)) = 0 Then
                ListBox1.RemoveItem i     ' Here the Board is Selected and other board are removed
            End If
        Next i
    Else
        With ListBox1
            .ColumnCount = 3
            .ListStyle = fmListStyleOption
            .MultiSelect = fmMultiSelectMulti
            .ColumnWidths = "35,55,75"
            .list = rngSource
        End With
    End If
End Sub
Public Sub Text_Current_Display(cur As Long)
    Dim checkItem, NewArray
    ListBox1.Clear
    ReDim NewArray(1 To mylist(cur).count, 1 To 3)
    For checkItem = 1 To mylist(cur).count
        NewArray(checkItem, 1) = mylist(cur)(checkItem)(0)
        NewArray(checkItem, 2) = mylist(cur)(checkItem)(1)
        NewArray(checkItem, 3) = mylist(cur)(checkItem)(2)
    Next
    ListBox1.list = NewArray
End Sub
Private Sub UserForm_Initialize()
    Dim myarray, intRow As Long
    With Sheet1
        intRow = .Cells(Rows.count, 1).End(xlUp).row
        myarray = Sheet1.Range("A2:C" & intRow).Value
        ComboBox1.list() = .Range(Cells(2, 1), Cells(intRow, 1)).Value
    End With
    Label2.Caption = 0
    Label4.Caption = 0
    With ListBox1
        .ColumnCount = 3
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        .ColumnWidths = "55,70,80 "
        .list = myarray
    End With
End Sub

Display More

SubirMehta · Jul 23rd 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Thanks

Unfortunatley i was not knowing the Collection object. Basically I learnt

Secondly i am not familar with For Each In Statements.
I guess i need to use the same for displaying all the Selected add Data in sheet4.
Can you tell me how.

Subir

**pike** · Jul 24th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Morning

A Collection with an array is easier to handle than an array of arrays

.. try ..

Code

Option Explicit
Dim mylist As New Collection
Private Sub cmdSelectAdd_Click()
    Dim myItem As Collection
    Dim iCount As Long, intRow As Long
    Dim blnSelected As Boolean
    
    blnSelected = False
    Label2.Caption = Label2.Caption + 1
    Set myItem = New Collection
    For iCount = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(iCount) Then
            blnSelected = True
            myItem.Add Array(ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2))
              With Sheet4
                intRow = .Cells(Rows.count, 1).End(xlUp).Row + 1
                .Range("A" & intRow).Resize(1, 4) = Array("Set:" & Label2.Caption, ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2))
              End With
        End If
    Next iCount
    If blnSelected Then
        mylist.Add myItem
    Else
        Label2.Caption = Label2.Caption - 1
    End If
End Sub
Private Sub CmdNext_Click()
    If Not Label4.Caption = Label2.Caption Then
        Label4.Caption = Label4.Caption + 1
        Text_Current_Display (Label4.Caption)
    End If
End Sub
Private Sub cmdPrevious_Click()
    If Not Label4.Caption <= 1 Then
        Label4.Caption = Label4.Caption - 1
        Text_Current_Display (Label4.Caption)
    End If
End Sub
Private Sub ComboBox1_Change()
    TextBox1.Text = ComboBox1.Text
End Sub
Private Sub CommandButton1_Click()
    TextBox1.Text = vbNullString
    ComboBox1.Value = vbNullString
    TextBox1_Change
End Sub


Private Sub TextBox1_Change()
    Dim iCount As Long, intRow As Long
    Dim rngSource
    
    With Sheet1
        intRow = .Cells(Rows.count, 1).End(xlUp).Row
        rngSource = .Range("A2:C" & intRow).Value
    End With
    If Not TextBox1.Text = "" Then
        For iCount = ListBox1.ListCount - 1 To 0 Step -1
            If InStr(1, LCase(ListBox1.list(iCount, 0)), LCase(TextBox1.Text)) = 0 Then
                ListBox1.RemoveItem iCount
            End If
        Next iCount
       
       Else
        ListBox1.list = rngSource
     End If
End Sub
Public Sub Text_Current_Display(cur As Long)
    Dim checkItem As Long, intItem As Long
    Dim NewArray()
    
    ListBox1.Clear
    ReDim NewArray(1 To mylist(cur).count, 1 To 3)
    For checkItem = 1 To mylist(cur).count
      For intItem = 1 To 3
        NewArray(checkItem, intItem) = mylist(cur).Item(checkItem)(intItem - 1)
      Next
    Next
    ListBox1.list = NewArray
End Sub
Private Sub UserForm_Initialize()
    Dim myarray
    Dim intRow As Long
    
    With Sheet1
        intRow = .Cells(Rows.count, 1).End(xlUp).Row
        myarray = Sheet1.Range("A2:C" & intRow).Value
        ComboBox1.list() = .Range(Cells(2, 1), Cells(intRow, 1)).Value
    End With
    Label2.Caption = 0
    Label4.Caption = 0
    With ListBox1
        .ColumnCount = 3
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        .ColumnWidths = "55,70,80 "
        .list = myarray
    End With
End Sub

Display More

SubirMehta · Jul 24th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Sorry Pike

You did not get me. i did not want to show Userform from the Module.

But what i wanted was to display in another sheet all the the Records of SelectedAdd Data in another words
want to display the Each Board (Record) with its Selected Added item (school) and area
For eg.
[TABLE="width: 245"]

[tr]

[TD="class: xl63, width: 64"]Board[/TD]
[TD="class: xl63, width: 110"]School[/TD]
[TD="class: xl63, width: 71"]Area[/TD]

[/tr]

[/TABLE]

[TABLE="width: 245"]

[tr]

[td]

ICSE

[/td]

[td]

Shishuvan

[/td]

[td]

Matunga

[/td]

[/tr]

[tr]

[td]

ICSE

[/td]

[td]

Bombay Scottish

[/td]

[td]

Mahim

[/td]

[/tr]

[tr]

[td]

SSC

[/td]

[td]

Donbosco

[/td]

[td]

Matunga

[/td]

[/tr]

[tr]

[td]

SSC

[/td]

[td]

DPYA

[/td]

[td]

Dadar

[/td]

[/tr]

[tr]

[td]

SSC

[/td]

[td]

Auxilium

[/td]

[td]

Wadala

[/td]

[/tr]

[tr]

[td]

IB

[/td]

[td]

SKN

[/td]

[td]

Bandra

[/td]

[/tr]

[tr]

[td]

CBSE

[/td]

[td]

St. Dominic

[/td]

[td]

Dadar

[/td]

[/tr]

[/TABLE]

Thanks
Subir

**pike** · Jul 24th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Hi SubirMehta,

Change this line

Code

.Range("A" & intRow).Resize(1, 4) = Array("Set:" & Label2.Caption, ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2))

to

Code

.Range("A" & intRow).Resize(1, 3) = Array( ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2))

SubirMehta · Jul 24th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Pike

Wonderful.

Just one thing to add one more command button I.E Display All SelectedAdd Records and remove
the following from cmdSelectAdd_click which is bold italics and highlighted

Code

Private Sub cmdSelectAdd_Click() 
    Dim myItem As Collection 
    Dim iCount As Long, intRow As Long 
    Dim blnSelected As Boolean 
     
    blnSelected = False 
    Label2.Caption = Label2.Caption + 1 
    Set myItem = New Collection 
    For iCount = 0 To ListBox1.ListCount - 1 
        If ListBox1.Selected(iCount) Then 
            blnSelected = True 
            myItem.Add Array(ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2))
'any chances
        '   With Sheet4 
         '       intRow = .Cells(Rows.count, 1).End(xlUp).Row + 1 
        '        .Range("A" & intRow).Resize(1, 4) = Array("Set:" & Label2.Caption, ListBox1.list(iCount, 0), ListBox1.list(iCount, 1), ListBox1.list(iCount, 2)) 
       '     End With        
'so how do we Redim Preserve Array myitem.array.......... as i find it difficult with Collection
End If 
    Next iCount 
    If blnSelected Then 
        mylist.Add myItem 
    Else 
        Label2.Caption = Label2.Caption - 1 
    End If 
End Sub

Display More

basically from this command button
i want the result for Sheet4

Code

Private Sub cmdDisplaySelectedRecords_Click()




'so that it retirves all what the user has selected 




End sub

Display More

attached file
Regards

Subir

**pike** · Jul 24th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Morning
Either way Collection or Array you will need to loop
The major advantage of the collection is that its can handle jaggered arrays

you can not redim the number of rows in a 2D array after it is first dimensioned

eg

Code

ReDim Selectedarray(1 To 3, 1 To 3) ' first dimensioning
 ReDim preserve Selectedarray(1 To 5, 1 To 3) 'errors

as you can only redim the second dimension

Code

ReDim preserve Selectedarray(1 To 3, 1 To 5) '

but is point less as you need to add more rows

Code

Private Sub cmdDisplaySelectedRecords_Click()
    Dim Selectedarray()
    Dim lngRecord As Long, RecordSet As Long, intItem As Long, arrayCount As Long
    
    If lngCount > 0 Then
        ReDim Selectedarray(1 To lngCount, 1 To 3)
        For lngRecord = 1 To mylist.count
            For RecordSet = 1 To mylist(lngRecord).count
                arrayCount = arrayCount + 1
                For intItem = 1 To 3
                    Selectedarray(arrayCount, intItem) = mylist(lngRecord).Item(RecordSet)(intItem - 1)
                Next
            Next
        Next
        ListBox1.list = Selectedarray
        Sheet4.Range("A2").Resize(lngCount, 3).Value = Selectedarray
    End If
End Sub

Display More

**pike** · Jul 25th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

with the collection you can add delete buttons
and add more control to the record sets

**pike** · Jul 25th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Hello SubirMehta,
or on a rainy cold winters day you could add some more controls to easily manipulate the collection data

to delete record sets or just items in the set
easy with collections hard with arrays

Code

If CLng(Label4.Caption) <> 0 And RecordSet Then
        Answer = MsgBox("Do you realy want to delete the selected Set items?", vbYesNo, "Delete Record Set")
        If Answer = vbYes Then
            If mylist(CLng(Label4.Caption)).count = ListBox1.ListCount Then
                For iCount = ListBox1.ListCount - 1 To 0 Step -1
                    If ListBox1.Selected(iCount) Then
                        checkCount = checkCount + 1
                    End If
                Next iCount
                If checkCount < mylist(CLng(Label4.Caption)).count Then
                    For iCount = ListBox1.ListCount - 1 To 0 Step -1
                        If ListBox1.Selected(iCount) Then
                            mylist(CLng(Label4.Caption)).Remove iCount + 1
                            lngCount = lngCount - 1
                        End If
                    Next
                    Text_Current_Display (CLng(Label4.Caption))
                ElseIf checkCount = mylist(CLng(Label4.Caption)).count Then
                    lngCount = lngCount - mylist(CLng(Label4.Caption)).count
                    mylist.Remove CLng(Label4.Caption)
                    If Label2.Caption = Label4.Caption Then Label4.Caption = Label4.Caption - 1
                    Label2.Caption = Label2.Caption - 1
                    If Label2.Caption <> 0 Then
                        Text_Current_Display (Label4.Caption)
                    Else
                        TextBox1_Change
                    End If
                End If
            End If
        End If
    End If

Display More

SubirMehta · Jul 25th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Thank you so much for the additional buttons which you have incorporated.

**pike** · Jul 25th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

glad to help on the project

SubirMehta · Jul 28th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Pike

Really Thanks for everything. Your code is perfectly working well

Actually i wanted specific things sending you new file.
I've deleted some of the command buttons which was not really required

Now i've shifted the code of cmdselectedAdd_click Button in Next click

The reason was with one cmd_Next click
1. Can read the current Record in the textbox and preared for next record to get entered
1. So when i select the board from combobox which is typed in Textbox and its respective items are shown listbox
I select them and it gets added and it
2. Displays the SelectedRecords same in listbox1
3. when i press next again for i am ready to do the same as i select the board from combobox typed in Textbox and its respective list is shown inlistbox . Again i select and it goes on

Basically with Next and Previous commands wanted to see the added selected items for that particular record of textbox1. with respective selection in Listbox
Everything happens simultaneously

Frankly speaking i found sligthly bit inconvenient and confussed pressing Cmd_Add-select button and Next and Previous button
Confussion : Did i press the next button to see the previous record of List items or did it match with my current record in Textbox

Also can we remove LAbel2 and and label4

I tried to combine in cmdNext the same but i got error in List_Current _Text
Invalid Procedure Call or Argument
and next time i clicked get error Subscript out of range

Thanks
Subir

**pike** · Jul 28th 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

hello
sure .. try ...

SamDsouza · Jul 31st 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Hi Subir

Have gone through the file attached. "Collection......1.xlsm"

Why you had to do all this when you could just select the the board from sheet1 column and paste it on sheet4.
Sorry that was on quite a jovial side. :thumbcoo:

I've put message box to see the differences as per the file attached.
I've also deleted the module which Pike sent as per the above mentioned file, because in the module it was all web related.

Some Observation
1. When you press next command 1st time or by mistake if it is pressed. You get an error as Invalid Procdure call or argument
The same is also observed when you press the Next command without selecting and adding your choice of items.
I really can't help you for how to overcome the Error Messages.
2. Also i've put messagebox to see Exactly what is happeneing.
3. When Next and Previous command are pressed your Listbox1 item is not matching with that of Textbox as well as combobox
Unfortunately your records are not moving together i.e as per Textbox1, and Combobox1 with Listbox? Why

Frankly speaking even i was looking for the similar requirement as yours.

Thanks and Regards

Sam Dsouza

SubirMehta · Aug 1st 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Sam

Thanks

It did not struck me to put message box to show the differences and to explain what i wanted.
You pointed out very well with messagebox.

I hope the masters will be able to resolve it.

Thanks

Subir

SamDsouza · Aug 1st 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Subir

As you desired Please find incorporate the changes i 've done

in the form have hidden all the label1 to label4 have also deleted cmdAddselect button and posted the same code in Private Sub Addselect

Have incorpoarted on Local Error before the redim stmt which i studied today
On Local Error GoTo errlcl
redim......
........
next
ListBox1.List = NewArray
errlcl: Resume Next

Please do the following changes in your next and Previous commands

Code

Private Sub CmdNext_Click()
Call SelectAdd_Click
List_Current_Display (CurRec)
    
    If Not Label4.Caption = Label2.Caption Then
        Label4.Caption = Label4.Caption + 1
        List_Current_Display (Label4.Caption)
    End If
    If Label4.Caption <> 0 Then List_Current_Display (Label4.Caption)
   
   If CurRec < 30 Then
       Board$(CurRec) = TextBox1.Text
       Institutes(CurRec) = ComboBox1.Text
       CurRec = CurRec + 1
       lblRecNo.Caption = Format$(CurRec)
       
       If CurRec > UBound(Board$) Then ReDim Preserve Board$(1 To CurRec)
       If CurRec > UBound(Institutes) Then ReDim Preserve Institutes(1 To CurRec)
    
      TextBox1.Text = Board$(CurRec)
     ComboBox1.Text = Institutes(CurRec)
    
       List_Current_Display (CurRec)
   End If
   
    
End Sub




Private Sub cmdPrevious_Click()
   If Not Label4.Caption <= 1 Then
        Label4.Caption = Label4.Caption - 1
        List_Current_Display (Label4.Caption)
    End If
    
    If Label4.Caption <> 0 Then List_Current_Display (Label4.Caption)
    
    
   
   If CurRec > 1 Then
      Board$(CurRec) = TextBox1.Text
      Institutes(CurRec) = ComboBox1.Text
      CurRec = CurRec - 1
      lblRecNo.Caption = Format$(CurRec)
      
      TextBox1.Text = Board$(CurRec)
      ComboBox1.Text = Institutes(CurRec)
      List_Current_Display (CurRec)
      
   End If
End Sub

Display More

Anything Else you want Ready to Help

Your Idea to have this and Pike to help you was mind blowing

Thanks

Sam

SubirMehta · Aug 2nd 2015

Re: Respective Selected Items to be displayed in Listbox as per the current record

Sam

Excellent Dear Friend. You have made my day.

But now only one issue remains.
for eg.
1st record i select from the listbox and Press Next command
for 2nd and 3rd i repeat the same lets say suppose for 4th record
Suppose i dont select anything from listbox and i press next command.
Guess What the textbox combobox record are not matching with each other for 4th and 5th.
the Listbox shows the 0 record of 5th record and for 4th record it displays selected items of 5th record
Can you help me this.
Ideally the result should be for 4th record the listbox should be empty and for 5th record it should display its selected List

By the way thank you so much for yours and Pike's real wonderful help.

Thanks
Subir

Respective Selected Items to be displayed in Listbox as per the current record

Participate now!

Share