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

SubirMehta · Jul 10th 2015

Hi all

Greetings

Sending you the attached xl.macro enabled file

I want to display the items selected ONLY from list box which needs to be stored in Array.

if you look at the textbox_change when i type few letters the list box pouplates items as per the
requirement.

I choose and select the items in Listbox for Record 1(as per the value in Textbox)
The SELECTION should be stored in an array (Not necessary all the items i will select)

When i press the Next or Previous button : the items in listbox as per the array should be displayed
in the listbox for that particular record i.e(cur%).

so when you press Next or Previous you see the record of Cur% in the Text box or Value of Board$(cur%)

I want to see the record of Board$(cur%) with respective selection in Listbox

Your Help is very much appreciated

Thanks

Subir

**pike** · Jul 10th 2015

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

Hi SubirMehta,
welcome to the forum
you have done a lot of good work behind the userform .. but I'm not quite sure what your question means can you explain more?

SubirMehta · Jul 11th 2015

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

Hello Pike

To be more clear

1. When i type the Board Values from Sheet in Text1.text the values in Listbox1.values are displayed. Kindly see the "Import" sheet.
2. The Listbox1 appears with Items that i want to Select
3. Now I would like to Select the Items from Listbox1. for eg i select any Three item if i type "SSC" in Textbox1
4. How Do i store the Selected/Checked Item From ListBox1 in an Array
5. Now very Crucial
When i Press Next I type in Textbox1.Text the value "ICSE" and i select any Two items of ICSE in the ListBox1 which gets stored in Array
(you can try the same in textbox1 by typing and pressing the next and Previous command buttons to see current record of Textbox1
In similar way when Next and Previous buttons are pressed the ListBox1 values should be displayed with the Selected values of that Current record of Textbox1.

Hope this Clarifies

Thanks

Subir

**pike** · Jul 11th 2015

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

Hi Subir,
you have one array why not make an array of arrays?
add a save button to save the array of listbox1 values to the array

**pike** · Jul 11th 2015

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

something like

Code

Option Explicit
Public list As New Collection




Private Sub Save_Collection_Click()
    Dim i As Integer, j As Integer
    Dim item As Collection
    If ListBox1.ListIndex <> -1 Then
        Set item = New Collection
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then
                item.Add ListBox1.list(i)
            Debug.Print ListBox1.list(i)
            End If
        Next i
        list.Add item
    End If
    Debug.Print list.count
    Debu

Display More

SubirMehta · Jul 11th 2015

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

Thanks
i need to check but what about its display

**pike** · Jul 11th 2015

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

Its a bit clunky but you can refine the syntax

try...

Code

Option Explicit
Dim cur As Integer
Dim board$()
Dim vtMonths() As Variant
Dim brd As Integer, count As Integer
Dim list As New Collection
Private Sub CmdNext_Click()
    count = 1
    Dim i As Integer
    Dim x As Integer
    Dim arrValues()
    Dim item As Collection
    If cur% > list.count Then
        Call Current_text
        lblSrNo.Caption = Format$(cur%)
       Set item = New Collection
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) Then
                item.Add TextBox1.Text & "|" & i
            End If
        Next i
        list.Add item
    End If
        Call Text_Current_Display(cur%)
   cur% = cur% + 1
End Sub
Private Sub cmdPrevious_Click()
  If cur% <> 1 Then
         cur% = cur% - 1
        lblSrNo.Caption = Format$(cur%)
        Text_Current_Display (cur%)
    End If
End Sub
Public Sub Current_text()
    board$(cur%) = TextBox1.Text
End Sub
Public Sub Text_Current_Display(cur%)
    Dim myarray
    Dim elist
    Dim mylist
    Dim checkItem
    TextBox1.Text = board$(cur%)
    For elist = 1 To list(cur%).count
        myarray = Split(list(cur%)(elist), "|")
        mylist = mylist & IIf(mylist <> "", "|", "") & myarray(1)
    Next
    TextBox1.Value = myarray(0)
    Call TextBox1_Change
    For Each checkItem In Split(mylist, "|")
        ListBox1.Selected(checkItem) = True
    Next
End Sub

Display More

SubirMehta · Jul 16th 2015

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

Hi Pike

No success with above code you sent.

Error at First Instance
"subscript out of range"

Code

Public Sub Text_Current_Display(cur%) 
   ........
TextBox1.Text = board$(cur%)
 .......

How about trying the below which i picked up from http://www.ozgrid.com/forum/showthread.php?t=173486

Code

Private Sub cmd1_Click() 
Dim x As Variant 
Redim x(0) 


For i = 0 To ListBox2.ListCount - 1 
If Me.ListBox2.Selected(i) Then 
x(UBound(x)) = Me.ListBox2.List(i) 
Redim Preserve x(UBound(x) + 1) 
End If 
Next i 


End Sub

Display More

Basically I want to display the Selected Items in List box of Each Current Record i.e cur% of Board$
i.e when Previous cmd button clicked or cmd button next button
I am not able to put the correct logic or code to show the Selected items in listbox of each current record typed in Textbox

Thanks

**pike** · Jul 16th 2015

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

can you please edit and add code tags to your post

SubirMehta · Jul 17th 2015

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

Sorry for the above mess Seems Wrong Tagging

Option1

Code

Private Sub cmd1_Click() 
Dim x As Variant 
Redim x(0) 




For i = 0 To ListBox2.ListCount - 1 
If Me.ListBox1.Selected(i) Then 
x(UBound(x)) = Me.ListBox1.List(i) 
Redim Preserve x(UBound(x) + 1) 
End If 
Next i 




End Sub

Display More

Option 2

Code

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)




Dim i           As Integer
Dim iCount      As Integer
Dim myInstitutes()  As String








Select Case KeyAscii
   Case Is = 13
   
iCount = 0
With ListBox1




    ReDim myInstitutes(1 To .ListCount)
    For i = 0 To .ListCount - 1
        If .Selected(i) = True Then
            iCount = iCount + 1              ' retriving nos of items selected
            myInstitutes(iCount) = .List(i)      'storing the itemsName in the listbox
        End If
    Next i




End With




If iCount > 0 Then
    ReDim Preserve myInstitutes(1 To iCount)
Else
    ReDim myInstitutes(1 To 1)
    myInstitutes(1) = "Nothing here!"
End If
 
   
End Select




End Sub

Display More

Basically i want to select the Institutes (items in Listbox) for that Cur% record of Board i.e Board$(cur%)
When Next and Previous Records are pressed the selected items or ticked marked items should be displayed of that Board$(cur%)

Thanks
Subir

EDIT
The "above mess" were posts with HTML formatting tags inside code tags, deleted.

**pike** · Jul 17th 2015

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

try.. this version

SubirMehta · Jul 17th 2015

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

Pike

Sorry

Have checked with your file . There are errors.

I am altogether attaching a new file. Please Run it and see whats happening by Pressing the Command Buttons
Have added here the combo box and Selecting items from combobox which is = to Textbox1.

The Listbox1 then acts as per Textbox

Command buttons Next and Previous are pressed but the Items in listbox disapaears. somtimes it appaears sometime it does not.
We need to correct it
After the correction as i desire that the display of selected institutes are displayed as per the Textbox1 Board$(cur%) record by pressing the previous and next buttons.

Thanks
Subir

**pike** · Jul 18th 2015

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

It really needs a add to records button and remove the sourcerow code

SubirMehta · Jul 20th 2015

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

Hi

Kindly check the following

Code

Private Sub cmdSelectAdd_Click()




ReDim myInstitutes(1 To ListBox1.ListCount)
icount = 0
For i = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(i) = True Then
         icount = icount + 1
         myInstitutes(icount) = ListBox1.List(i)
       End If
     Next i




If icount > 0 Then
    ReDim Preserve myInstitutes(1 To icount)
    MsgBox myInstitutes(icount) & " " & myInstitutes(icount,1) & " " & myInstitutes(icount,2) & " " icount
Else
    ReDim myInstitutes(1 To 1)
    myInstitutes(1) = "Nothing here!"
End If








''''''How to determine that how many times cmdSelectAdd is pressed and then How to get Total of myInstitutes




End Sub












Private Sub cmdShowAddedRecords_Click()




Dim j As Integer, row As Integer
Dim cur As Integer
Dim icount As Integer
Dim myInstitutes() As Variant
row = 1




For j% = 1 To UBound(myInstitutes)     




Sheet4.Cells(row, 1) = myInstitutes(j)
Sheet4.Cells(row, 2) = myInstitutes(1, j)
Sheet4.Cells(row, 3) = myInstitutes(2, j)
        row = row + 1




Next j




'''''''Why no record Showing in sheet4 and also want to show the Grand total of Selected items




End Sub

Display More

Please help me with the questions asked in the procedures itself

Thanks

**pike** · Jul 21st 2015

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

That will be easier to work with and understand ..will have a look tonight

**pike** · Jul 21st 2015

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

can you attach the workbook so I can see you intent and context

SubirMehta · Jul 21st 2015

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

Pike

Please find the attached file

Thanks

Subir

**pike** · Jul 22nd 2015

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

Hi SubirMehta
This is my take

SubirMehta · Jul 22nd 2015

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

Thanks Pike

FYI I've not used Toggle Button. May i know why you have used Toggle Button ?

It is confussing when Pressing Toggle Button and rest other Command Buttons.
Something is really happening but due to confussion not able to understand what is that really happening.

Regards

Subir

**pike** · Jul 22nd 2015

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

Hello
It's only my take on your request
It separates the two tasks
Searching with record search
Viewing saved searches

I think the two need separating
What is the process you are following?

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

Participate now!

Share