vba excel Multi Select List Box

[MSingh]

Hi,

I was referred to this site by a MVP, I would appreciate your help.

I have a multi select list box with 2 columns, how do i amend Ron De Bruin's Last Row macro such that the multi selection
is copied: This is my current amendment to Ron's code. The function that goes with this code is copied as Ron instructed.

Sub LastRow_Example()
Dim LastRow As Long
Dim rng As Range

Set rng = Sheets("Sheet1").Range("d14:D213")

' Find the last row
LastRow = Last(1, rng)

' After the last row with data change the value of the cell in Column d
rng.Parent.Cells(LastRow + 1, 4).Value = Me.ListBox2.Value
End Sub

[HansV]

Welcome to Eileen's Lounge!

Try this version:

Code: Select all

Sub LastRow_Example()
  Dim LastRow As Long
  Dim rng As Range
  Dim n As Long
  Dim i As Long

  Set rng = Sheets("Sheet1").Range("d14:D213")

  ' Find the last row
  LastRow = Last(1, rng)

  ' After the last row with data change the value of the cell in Column d
  For i = 0 To Me.ListBox2.ListCount - 1
    If Me.ListBox2.Selected(i) Then
      n = n + 1
      rng.Parent.Cells(LastRow + n, 4).Value = Me.ListBox2.List(i)
    End If
  Next i
End Sub

For others reading this thread: the Last function can be found at Find last row, column or last cell on Ron de Bruin's website.

[MSingh]

Dear Hans,

Thank you for such a speedy response.

May i raise 2 further queries:

1. regarding the amemdment to Ron De Bruin's programming for "Last..." & your solution: Your solution works perfectly, thank you. Would it be correct for me to add before the "End If"

Listbox2.Selected(i)=False
so that the selections are cleared?

2. the very same user form has a second page, with 2 columns, column 2 is the bound column.
I cannot seem to get the bound column2 to copy to Sheet1 Range("D14:D213"). As the code stands is copies the multi selection from column 1. Page 2 has ListBox1.

Your assisstance is appreciated.
Regards
Mohamed

[HansV]

Hi Mohamed,

1. Yes, that would be the correct way to clear the selection in the list box.

2. To copy the value of the second column of a list box, you'd use a line such as

rng.Parent.Cells(LastRow + n, 4).Value = Me.ListBox1.List(i, 1)

The second argument of the List property is the zero-based column number, so List(i, 0) would be an item in the first column, List(i, 1) an item in the second column etc. If you omit the column number, it is assumed to be 0.

[MSingh]

Hi Hans,
Thank You-your code worked perfectly!

Best Regards
Mohamed

[HansV]

You're welcome!

[MSingh]

Hi,

The list box whose code you so kindly supplied has to check cell D213 before pasting the selection. The Userform is made up of 10pages each with a list box of a different named range. Is there a way to write 1 code & call it each time to check cell d213 or do i have to write a separate code for each paste command button? The Select case works, but it would be most efficient if there could be 1 sub that is called each time.

Thanking you
Mohamed

[HansV]

It may well be possible to create a common function or procedure, but I'd have to know more about the workbook and userform. Would it be possible to create a copy of the workbook, remove all superfluous information, as well as sensitive/proprietary information, and attach it to a reply? Thanks in advance.

[MSingh]

Hi Hans,

Certainly.

I'll work on a simplificaion.

Thnaks
Mohamed

[HansV]

I have received your e-mail. Since the On Click event procedures for the command buttons look very similar, it is indeed possible to extract a common part:

Code: Select all

Private Sub HandleClick(BatchNo As Long, CompanyNo As Long)
  Dim LastRow As Long
  Dim rng As Range
  Dim n As Long
  Dim i As Long
  Dim lngFirst As Long
  Dim lngLast As Long

  lngFirst = 207 * BatchNo - 191
  lngLast = lngFirst + 199

  Set rng = Sheets("Batch Setup").Range("D" & lngFirst & ":D" & lngLast)
  ' Find the last row
  LastRow = Last(1, rng)
  If LastRow = lngLast Then
    MsgBox "The table for batch " & BatchNo & " is full", vbExclamation
    Exit Sub
  End If

  ' After the last row with data change the value of the cell in Column D
  With Me.Controls("ListBox" & (CompanyNo + 4))
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        n = n + 1
        rng.Parent.Cells(LastRow + n, 4).Value = .List(i)
        .Selected(i) = False
      End If
    Next i
  End With
End Sub

You then need only one line in each On Click event procedure:

Code: Select all

Private Sub cmd1Co1_Click()
  HandleClick 1, 1
End Sub

Private Sub cmd1Co10_Click()
  HandleClick 1, 10
End Sub

...

Private Sub cmd1Co9_Click()
  HandleClick 1, 9
End Sub

Private Sub cmd2Co1_Click()
  HandleClick 2, 1
End Sub

...

[MSingh]

Hi Hans,

Magic, pure Magic!!!
Code works perfectly.
Thanks a million!

Kindest Regards
Mohamed