Solved: Listbox+rangecopy+concatenate

[jmaocubo]

HI...
1)I have a lisbox and i'm trying to select a range of cells;
2) when i click in a botton, it copy that range (excluding the blank cells) and paste in the lisbox;
3) then select a random cell and in the listbox click in another botton to copy the values, but concatenate all values (the values are text) to the cell selected

I'm sorry for my English....
Can anyone help me?

Thanks in advance

[mikerickson]

What kind of List box are you using? On a userform? ActiveX? Forms menu?

[jmaocubo]

I'm using a userform

[mikerickson]

This is the code for the attached userform, which has 2 RefEdit controls, 2 command buttons and one multi-select list box.
Private Sub CommandButton1_Click()
Dim selectedRange As Range, oneCell As Range

Set selectedRange = RangeFromRefEdit(RefEdit1)

If selectedRange Is Nothing Then
MsgBox "Please select a range."
RefEdit1.SetFocus
Else
With ListBox1
.Clear
For Each oneCell In selectedRange
.AddItem CStr(oneCell.Value)
Next oneCell
End With
End If
End Sub

Private Sub CommandButton2_Click()
Const Delimiter As String = ", "
Dim selectedRange As Range
Dim builtString As String
Dim i As Long

Set selectedRange = RangeFromRefEdit(RefEdit2)

If selectedRange Is Nothing Then
MsgBox "Please select a range."
RefEdit2.SetFocus
Else
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
builtString = builtString & Delimiter & .List(i)
End If
Next i
builtString = Mid(builtString, Len(Delimiter) + 1)
End With

selectedRange.Cells(1, 1).Value = builtString
End If
End Sub

Private Sub UserForm_Initialize()
ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

Function RangeFromRefEdit(aRefEdit As Object) As Range
On Error Resume Next
Set RangeFromRefEdit = Range(aRefEdit.Value)
On Error GoTo 0
End Function

[jmaocubo]

Hi mikerickson

Thanks, works fine!!!!!!:friends:

The only thing i can't understand is:
I'm trying to create a module:
Sub call ()
call userform1
end sub

But gives me error on the module and don't open the userform!!!

Why?

Thanks again....

[BrianMH]

think you need
userform1.show

[mikerickson]

I'd also avoid naming a proceedure "Call", since it is a VBA verb.
This routine worked for me.
Sub makeUF()
UserForm1.Show
End Sub

[jmaocubo]

it's working very well....

Many Thanks!!!! :beerchug: