Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiselect from multicolumn listbox (XL2000)

    Hi

    I haven't found anything similar when searching for "Combobox OR Listbox" in the archives. I'm trying to get a listbox to display several non-sequential columns and "multiselect" is enabled. This is beyond simply linking to a single cell; I have a similar solution from XL95 using dialog sheets, but I can't get it to work with a userform in XL2000 .

    I want the user to be able to select multiple list items and have each of the choices transferred into the worksheet, beginning with the second row (A2), with only certain values taken from the listbox as indicated below. I need show several columns so that the user can choose the proper item(s) from 2 or more possible choices where the only difference is in the start & end dates.

    I've put question marks(?) where I don't know what to do next. Thanks in advance.

    AJF

    ________________________________
    Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim rArray As Variant
    ' this is a dynamic array [=OFFSET(Schedule!$A$1,1,0,COUNTA(Schedule!B:[img]/forums/images/smilies/cool.gif[/img],16)],since the Schedule sheet changes on a regular basis
    rArray = Range("Schedule")
    ' fill ListBox1 with content from a worksheet range
    Caption = "Please choose from the following..."
    With Me.ListBox1
    .Clear ' remove existing content
    .ColumnCount = 16
    .ColumnWidths = "30,50,50,150,0,40,40,0,0,0,0,0,0,0,0,15"
    .List() = rArray
    .ListIndex = -1 ' no selected item
    ' .RowSource = Range("Schedule").Address ' unfortunately, this doesn't seem to work when the array is dynamic
    ' .ColumnHeads = True ' the row above the RowSource range will be used as headings
    End With
    End Sub
    ___________________________________

    Private Sub OKButton_Click()
    ' moves all selected items from the listbox to the Setup worksheet
    Dim i As Integer
    Application.ScreenUpdating = False
    With Me.ListBox1
    ' Set i = Range(i).Cells(1, 1)
    For i = .ListCount - 1 To 0 Step -1
    If .Selected(i) Then
    ?.List(i + 1, 0) = i?

    ' I've tried (i +1, 0) thru (i +1, 16) using only the listbox columns I want as well as trying other methods but they don't work I want to fill each of the following cells for each "i" row selected in the listbox.

    ?1st Cell in Row? = ?1st Column in Listbox? ' the value in the 1st listbox column is from the ROW() Function of the Schedule sheet needed for the VLOOKUP below.

    ?2nd " " " ? = "" ' empty cell for user input
    ?3rd " " " ? = ???????????????????? ' is it possible to insert only the value by CONCATENATEing the values in columns 2, 4 & 16 of the listbox???, otherwise, I'll just have to use the formula instead.

    ?4th " " " ? = ""
    ?5th " " " ? = ""
    ?6th Cell?.FormulaR1C1 = "=NETWORKDAYS(RC[2],RC[3],Holidays)"
    ?7th Cell?.FormulaR1C1 = "=SUM(NETWORKDAYS(RC[1],RC[2],),-NETWORKDAYS(RC[1],RC[2],Holidays))"
    ?8th Cell?.FormulaR1C1 = "=VLOOKUP(RC[-7],Schedule!,6,FALSE)"
    ?9th Cell?.FormulaR1C1 = "=VLOOKUP(RC[-8],Schedule!,7,FALSE)"

    ' with Cells 8 & 9 above, I would rather take the values of columns 6 & 7 of the listbox and code in the Conditional Formatting so that if the text value does not match the formula, the cell(s) would be RED & BOLD font with YELLOW background that way if the Schedule sheet (which is updated regularly from a delimited text file) is changed, then the user can be alerted to the fact that the scheduled values have changed.

    ?10th Cell?.FormulaR1C1 = "=VLOOKUP(RC[-9],Schedule!,8,FALSE)"

    ' alternatively, with 10th, 11th & 12th cell I could use the value from the listbox where the columnwidth is "0"

    ?11th Cell?.FormulaR1C1 = "=VLOOKUP(RC[-10],Schedule!,9,FALSE)"
    ?12th Cell?.FormulaR1C1 = "=VLOOKUP(RC[-11],Schedule!,11,FALSE)"
    ?13th Cell?.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" ' that's all

    End If
    Next i
    Application.ScreenUpdating = True

    Unload Me
    End Sub

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Multiselect from multicolumn listbox (XL2000)

    Hi AJF

    <<< I haven't found anything similar when searching for "Combobox OR Listbox" in the archives. I'm trying to get a listbox to display several non-sequential columns and "multiselect" is enabled. >>>

    Call me crazy, but why can't you have a special range made out of 16 columns to have all these columns sequencial?

    I mean you have 256 Columns, and surely the code to update these columns, or even formulas, will not add that much overhead to the workbook.

    My suggestion is to reserve the source to fill the ListBox to the side, and be done with it. Think how easy the code will be.

    Again this is my opinion.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiselect from multicolumn listbox (XL2000)

    Wassim,

    I guess I could do something there. The deal is that the original delimited text file is downloaded from the web every month or so and on average it has anywhere from 2,800 to 3,000 rows. We have no control on how it is presented.

    BACKGROUND--When I copy the text file and paste and then do text to column, that results in the 15 columns. In column 16, I put a formula to look at the State name in Column 3 and create the state abbreviation, ie Texas =TX. Everytime the textfile is updated, I can just do what I did ealier starting with Cell A2, this leaves the header row intact and leaves column 16 alone.

    I'm experimenting with a macro to automatically import the text file, but I'm leary about fiddling with that to make it output to just columns I need. Because each column has important data, I just need a few of them in relation to the active worksheet.

    But let's say you're not crazy and I do create a special range to catch the 4 or 5 critical columns and make the listbox just reflect those 4 or 5. Then what?, How do I print to the worksheet those columns from the selected rows of the listbox.

    SCENARIO--The listbox shows 3,000 entries, I want to select 10 different locations, of which 2 are for the same location but with different start/end dates.

    How do I get those 10 choices into the spreadsheet, by rows, and I would still like to add the extra columns that calculate for NETWORKDAYS and Holidays and SUM the values of 3 different cells?

    Maybe I should focus on that first, once I can make the transfer work, then I can fiddle with the other "enhancements.

    Thanks anyway, I'm going to experiment with creating the "essentials" array.

    later
    AJF

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Multiselect from multicolumn listbox (XL2000)

    Hi AJF

    OK so it is a text file that you are manipulating. So I suggest the following steps:

    1) Import the Text file into a worksheet. Call this worksheet "Raw Data". This will also split the text file into columns.
    2) In another worksheet call it "Usable Data" you will rearrange the columns in the way you want them. This will be the same data as in "Raw Data" but it will be arranged in the way you want it to be, and if you ever need to revert simply recopy the "Raw Data" contents.
    3) On a User Form you will have a control (either a dropdown or a ListBox) for locations, and another control for Starting date and another for ending date.

    The User will select a location, a starting date, and ending date or a multiple of each of these depending on your preferences.

    The code will process the User selections and filter the data in "Usable Data" based on user selections and then copy the filtered data, which now represents what the the User has selected, into a new sheet call it "Filtered Data".

    Then you will add your formulas to calculate the NETWORKDAYS, Holidays and SUM and display it for the User.

    I don't know how the data is presented to you, but try using Excel's Text Import wizard first and see how good a job it does.

    I see this a an easy project, but again you are the one who needs to do the work. If you need any help let me get a sample of the data in its raw form, this does not need to be actual data but some sample of it and I'll help you with the import.

    Happy Holidays

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiselect from multicolumn listbox (XL2000)

    Happy Holidays back to you, Wassim,

    I guess my 1st step is to learn how to transfer a selected listbox line with multiple columns to the worksheet, and loop it for each selected line--I don't know how to do this.

    Keep in mind that the "List" function is preferred because the source has up to 3,000 rows and normally, the user might pick between 2 and 10 or 15 items, but occasionally as high as 100 different items.

    Once I get a handle on that step, I can start experimenting with outputting the desired columns and adding formulas to the mix. The goal is to extract only the values of those 7 columns from a selected line in that listbox. Yet the output row will be 13 columns wide, with formulas and empty cells in between the 7 that I want.

    FWIW...these extra cells will be acted upon based on manual user input which can't be automated (i.e., official end date may be 30 Dec 2001, but the user ultimately may want to span a couple of start/end dates or whatever for planning purposes on yet another sheet. The formulas also make it easier to test some assumptions about costs vs. timeline. The idea is to make the codes as tight as possible for the creation of a template (*.xlt). Then with every new proposed project, an instance of this template is made for planning/costing/bidding purposes.

    At that time, i will move the "Schedule" sheet to its own XLS file so that any copies of these proposal workbooks will refer to this for the current data, which in turn is downloaded from the master text file which is online, by a macro based on a date event ( i.e. every 1st of the month automatically).

    That way no one needs to update the schedule sheet. That's also why I want to do a conditional formatting in some of the cells, so that the user is alerted to the fact that the original assumptions are no longer valid, i.e. the Department of Labor rates have changed or the effective end date has been changed from above.

    My experimental workbook is currently about 300kb, I suppose I could clean a copy and post it--especially all the garbage code I have in the modules as I hunt for a solution :<), but first I need to learn how to do the basics in the 1st sentence above.

    Thanks again

    AJF

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Multiselect from multicolumn listbox (XL2000)

    AJF

    I will answer you point by point.

    <<< I guess my 1st step is to learn how to transfer a selected listbox line with multiple columns to the worksheet, and loop it for each selected line--I don't know how to do this. >>>

    Check the <font color=red> MultiSelect, Selected Properties Example </font color=red> in the On-line Help. It has all you need.

    Basically your Users will select items from the ListBox, as many as they want, 1 or 2 or all it does not matter.
    Then the code will find all the items that the Users picked.
    Then the code will use these items to filter the data and come up with the results.

    The OLH has a lot of examples.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Multiselect from multicolumn listbox (XL2000)

    AJF

    <<< The goal is to extract only the values of those 7 columns from a selected line in that listbox. Yet the output row will be 13 columns wide, with formulas and empty cells in between the 7 that I want. >>>

    For this you will need to use the .ListIndex of the Selected Item to and correlate it to the row that you need to get the info from.

    So if you fill the ListBox from range("A2:A10") and the User selects the first Item in the list that means .ListIndex = 0 (See the help where they tell you that ListIndexes are zero based) and you need to return the second row, so the .Row = .ListIndex + 2.

    If the User selects the 3rd item in the list box => .ListIndex = 2 and the 3rd item in the range would be at Row 4 (Row2 =1,Row3=2, and Row4=3) again making .Row = .ListIndex+2

    So here we see that the row can be figured out easily based on the User picking from the List.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiselect from multicolumn listbox (XL2000)

    Wassim, I've cleaned up the file and shrunk it enough to show you what I'm after.

    I think the ".ListIndex" only works on listboxes with fmMultiSelectSingle only. One has to use ".Selected" property for fmMultiSelectMulti & fmMultiSelectExtended.

    I ran all over the Helpfiles as suggested, as usual, the people who write these are clueless on practical realworld examples. The example they gave for multi columned and multiselections listbox was to transfer them to another listbox--that's it!!--no example on how to loop those selections to a specified worksheet starting with one row until the end.

    I'd appreciate just getting the snippet of code to show how I can take each selection and for every column in the list box to fillout each cell accordingly before going to the next row and filling each cell in that row as well.

    If I have that, I think I can figure out how to transfer some of the columns and not the others of any given selection.

    For practical purposes, when transfering the values of a couple of the listbox columns, I'd like to append a conditional formatting formula so that anytime in the future, if the recorded values do not match the official values of the source sheet/file, those cells would be flagged to the user.

    I guess an ugly way to do it would be to take the first row of my spreadsheet and pre-fill all the VLOOKUP or SUM formulas & conditions in the requisite cells as my "master row", then prior to the loop, use the count property to count the number of selections in the listbox and have the master row copied and inserted "X" times, then have the loop print only the first column(indexcolumn) values to the spreadsheet. That way, the prefilled formulas would then lookup the source again to fill out the rest of the data.

    Once I get this working fine in the xlt template form, I think I may have this sheet be in its own file and have a time-event macro automatically update this, so that all future workbooks generated from the template will reference this master file. That way even a year from now, a pre-existing active workbook can be kept current. Any workbook(s) that are archived can simply have formulas pasted for values.

    Oh well, thanks

    Thanks

    AJF
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •