View Full Version : Solved: Listbox+rangecopy+concatenate
jmaocubo
04-12-2011, 08:01 AM
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
04-12-2011, 11:27 AM
What kind of List box are you using? On a userform? ActiveX? Forms menu?
jmaocubo
04-13-2011, 05:36 AM
I'm using a userform
mikerickson
04-13-2011, 07:18 AM
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
04-14-2011, 07:39 AM
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
04-14-2011, 08:33 AM
think you need
userform1.show
mikerickson
04-14-2011, 01:01 PM
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
04-18-2011, 11:06 AM
it's working very well....
Many Thanks!!!! :beerchug:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.