Posts by SubirMehta

Re: How to Insert New set of Records in List box between the 2 set of Records of List

Anyone who could help me for a code Inserting a new Record in Listbox for saving new items between the two Saved Selected records of Listbox.

Thanks in Advance

Subir M.

Anyone

I am able to to smoothly do Previous and Next records of textbox records and Listbox records

Now for eg

for 1st set of records i select and add the (School) items in the Listbox
for 2nd and 3rd also i repeat the same.

No if i want to insert a record(Board) between 2nd and 3rd the problem is i am not able to do for the listbox
eg
when Inserted at 3rd record. The Listbox values of 3rd record should become empty and ready for New selection of record and When Next is Pressed it should show the list of 3rd record now as 4th Record.
You can check for textboxes. It is working perfectly but need help for Listbox. Bascially everything moving Smoothly

Attached is the File for reference

Thanks in Advance

Subir M.

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

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

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

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

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

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

basically from this command button
i want the result for Sheet4

Private Sub cmdDisplaySelectedRecords_Click()




'so that it retirves all what the user has selected 




End sub

attached file
Regards

Subir

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"]

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

[/TABLE]

[TABLE="width: 245"]

ICSE

Shishuvan

Matunga

ICSE

Bombay Scottish

Mahim

SSC

Donbosco

Matunga

SSC

DPYA

Dadar

SSC

Auxilium

Wadala

IB

SKN

Bandra

CBSE

St. Dominic

Dadar

[/TABLE]

Thanks
Subir

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

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

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

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

Pike

Please find the attached file

Thanks

Subir

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

Hi

Kindly check the following

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

Please help me with the questions asked in the procedures itself

Thanks

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

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

Sorry for the above mess Seems Wrong Tagging

Option1

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

Option 2

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

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.

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"

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

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

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

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

Thanks
i need to check but what about its display

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

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