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

  • 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

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


    afternoon .. replace all the code in the userform with

  • 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

  • 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 ..


  • 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

  • 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))
  • 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



    basically from this command button
    i want the result for Sheet4



    attached file
    Regards


    Subir

  • 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




  • 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


  • 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

  • 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

  • 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

  • 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


    Anything Else you want Ready to Help


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


    Thanks


    Sam

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!