Populating List box with data from other excel file

Quote:

---

Originally Posted by surya prakash

Hello John,

The listbookdata.xls, which contains the data for populating the listbox is available to users on a network; I am wondering, if the invisible filesharing might create some problems.

Also, after populating the listbox, (I changed the multiselect property of the listbox to fmMultiSelectExtended), the insert command is not copying the multiple selection that I made in the list box. Can you please check.

thanks
prakash

---

Hi prakash,

1) What sort of problems do you mean?

2) The example given of how you wanted your output had the entire (separate) list of each "class" or "item" (wall, floor, roof) in the A to D columns of the ListBook. So that's exactly how I set it up - to simply copy ALL the walls, or ALL the floors, or ALL the roofs into those columns with the "Insert Selection" button you had.

To do what you appear to want to do now would require something different altogether as we would then be looking not only at classes, but at sub-classes as well - do you mean: a) multi-select before or after using the option button and then b) use your Insert Selection button, or to c) use a Double-Click event to insert the multi-selection?

John

Hi John,

Thank you for your patience; may be I have not explained clearly.

You are right, the whole idea of having a list box is to make multiple selections and only the selections are to go to the listbox.xls file.

To simplify, we can have three worksheets in listbox.xls, ?Floor Report?, ?Roof Report? & ?Wall Report?. The data based on the type can be exported to one of these sheets.


Thanks
Prakash

Hi John,

I forgot to answer your second part of the question:

1) The user makes the multi-select after the option box select.
2) user may make selection in data available from different option box clicks
3) Finally, the data selected should be exported to the listbox.xls after clicking on the insert button.

thanks
prakash

Hi prakash,

In response to your earlier posting I have added another button to your form and the following procedure:

'//insert selected items only
'//(hold "Shift" and click individual items to select them)

Code:

---

Private Sub InsertSelection_Click()
Dim i%, j%, N%
'//copy and paste heading
      Windows("ListBookData.xls").Activate
      Range("A1:D1").Select
      Selection.Copy
      Windows("ListBook.xls").Activate
      Worksheets("Report").Activate
      If Range("A1") = Empty Then
            Range("A1").Select
      Else
            Range("A65536").End(xlUp).Offset(11, 0).Select
      End If
      ActiveSheet.Paste
      Application.CutCopyMode = False
'//insert selected items
      For i = 0 To 49
            If ListBox1.Selected(i) = True Then
                  For j = 0 To 3
                        ActiveCell.Offset(0, j) = ListBox1.column(j, i)
                  Next j
            End If
            ActiveCell.Offset(1, 0).Select
      Next i
'//move up to the "Data" row & count rows
      N = 0
      Do Until Selection = "Data"
            ActiveCell.Offset(-1, 0).Select
            N = N + 1
      Loop
'//select all this...
      Range(ActiveCell, ActiveCell.Offset(N, 3)).Select
'//sort to get rid of empty rows
      Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'//put in borders (go to another sub for this)
      FormatCells
'//unload userform
      Unload Me
End Sub

---

This allows you to select individual items from anywhere from either the main list, or, from one of the class lists.

For example, you may select one each from Roof, Floor, and Wall, OR, 3 or 4 of each from either Roof, Floor, or Wall etc. and these are put on your 'Report' sheet...(Have a look at the attachment).

If you now want (as it appears) to these put on different sheets - I think I've given you enough to work on to do this yourself, or, to put this up as a separate post...

Regards,
John

Thank you John for your time and effort

Hi prakash,

Glad I was able to be of some help to you...

Regards,
John