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

    Listbox multiple selections (XL 2000)

    Happy New Year XL Loungers,

    I posted this problem earlier, but didn't find the answer(s) I was looking for, (Wassim, I do appreciate the assist on the previous posts). I'm attaching a sample to show what I'm after. From the listbox, without using the active cell routine, how do I get the multiple selections onto another sheet (SCHEULE)? If I can at least do that and for each selection, learn how to populate additional cells per selection, then I would really be off to a happy new year ;<)

    I've visited more excel-sites than I care to count to no avail on how to get multiple selections into the spreadsheet? Most tell me how to return the selections to a message box.

    I have a source array that is 15 columns by about 2000 rows--No problem getting that into the listbox, including the 6 columns that are needed to be visible for making the correct selection(s) based on city and season of the year.

    Ideally, I want to just capture the index numbers and via code, run a series of lookups on the source cells to return values for 6 columns; and return a formula for another 9 columns which act on the selected info. The result would be 18 columns and a row for each selection and start in A2.

    In that vein, I may eventually have 2 command buttons, one to "Update" by counting used cells in the SCHEDULE sheet and adding new results in the first available empty row, so that the user could amend the sheet without starting all over. And the "New" button would first clear the sheet, then fill the SCHEDULE sheet.

    Thanks

    AJF
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Listbox multiple selections (XL 2000)

    Hi,
    To get your index values into column 1 you could change your commandbutton1 code to something like:
    <pre>Private Sub CommandButton1_Click()
    ' moves all selected items from the listbox to the Setup worksheet
    Dim i As Integer, SelectedItemList As Integer, lngRow As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    lngRow = 0
    With Me.ListBox1
    For i = .ListCount - 1 To 0 Step -1
    If .Selected(i) Then
    Sheets("Schedule").Cells(lngRow + 2, 1) = .Column(0, i)
    lngRow = lngRow + 1
    End If
    Next i
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    Unload Me
    End Sub
    </pre>

    Within the loop you could also set the formula for the other columns for each row as necessary.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Listbox multiple selections (XL 2000)

    Rory, Thanks a million.

    That's what I was missing; Dimming lngRow As long and referencing it in the loop as .Cells(lngRow + 2, 1).

    Thanks again, I think I can get the rest on my own including the two commandbuttons for "New" & "Update"

    later
    AJF

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

    Re: Listbox multiple selections (XL 2000)

    I thought I had the rest in hand, but every variation that I try, when using the "With" statement it keeps returning an error. Also I'm not having any luck with getting a formula and then returning only the value to any particular cell. I've shortened my code to just a single example of each. Thanks in advance.

    AJF
    If .Selected(i) Then
    Sheets("Schedule").Cells(lngRow, 1) = .Column(0, i)

    'now calling out 17 additonal columns on the same row

    Sheets("Schedule").Cells(lngRow, 2).Value = ""
    Sheets("Schedule").Cells(lngRow, 3).FormulaR1C1 = "=CONCATENATE(VLOOKUP(RC[-2],TravelRates,4,FALSE),"", "",IF(VLOOKUP(RC[-2],TravelRates,2,FALSE)=""CONUS"",VLOOKUP(RC[-2],TravelRates,15,FALSE),VLOOKUP(RC[-2],TravelRates,2,FALSE)))" 'How do I lookup, then return the value?

    Sheets("Schedule").Cells(lngRow, 11).FormulaR1C1 = "=VLOOKUP(RC[-10],TravelRates,8,FALSE)"
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions.Delete
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions.Add Type:=xlExpression, Formula1:="=VLOOKUP(RC[-10],TravelRates,8,FALSE)>RC[11]"
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions(1).Font.Bold = True
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions(1).Font.ColorIndex = 3
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions(1).Interior.ColorIndex = 6
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions.Add Type:=xlExpression, Formula1:="=VLOOKUP(RC[-10],TravelRates,8,FALSE)<RC[11]"
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions(2).Font.Bold = True
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions(2).Font.ColorIndex = 5
    Sheets("Schedule").Cells(lngRow, 11).FormatConditions(2).Interior.ColorIndex = 34

    ' and so on

    lngRow = lngRow + 1
    End If

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Listbox multiple selections (XL 2000)

    Hi,
    Try something like:
    With Sheets("Schedule")
    .Cells(lngRow, 2).Value = ""
    With .Cells(lngRow, 3)
    .FormulaR1C1 = "=CONCATENATE(VLOOKUP(RC[-2],TravelRates,4,FALSE),"", "",IF(VLOOKUP(RC[-2],TravelRates,2,FALSE)=""CONUS"",VLOOKUP(RC[-2],TravelRates,15,FALSE),VLOOKUP(RC[-2],TravelRates,2,FALSE)))"
    .Formula = .Value
    End With
    With .Cells(lngRow, 11)
    .FormulaR1C1 = "=VLOOKUP(RC[-10],TravelRates,8,FALSE)"
    .Formula = .Value
    With .FormatConditions
    .Delete
    .Add Type:=xlCellValue, Operator:=xlGreater, _
    Formula1:="=RC[11]"
    End With
    With .FormatConditions(1)
    With .Font
    .Bold = True
    .ColorIndex = 3
    End With
    .Interior.ColorIndex = 6
    End With
    With .FormatConditions
    .Add Type:=xlCellValue, Operator:=xlLess, _
    Formula1:="=RC[11]"
    End With
    With .FormatConditions(2)
    With .Font
    .Bold = True
    .ColorIndex = 5
    End With
    .Interior.ColorIndex = 34
    End With
    End With

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Listbox multiple selections (XL 2000)

    I get it!!
    I got it!!
    I'm groovin on!! <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    Thanks Rory

    AJF

Posting Permissions

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