View Full Version : Solved: textbox value = cell range

11-01-2010, 06:42 AM
Hello to all:
On Userform1 I have Listbox1 that imports a chosen Access Table and inserts the data in Sheet1 Range A2:B

I have a textbox that returns the value of A2 (of sheet1)

Me.TextBox1.Text = CStr(ThisWorkbook.Sheets("Sheet1").Range("A2").Value)

when I change it to

Me.TextBox1.Text = CStr(ThisWorkbook.Sheets("Sheet1").Range("A2:B").Value)
it does not work.

Can someone help with this?

11-01-2010, 10:16 AM
Range("A2:B") is not a valid range

11-01-2010, 10:36 AM
Thank you and you are correct . The code should read

or maybe similar to (this would be better):

Dim SrngAdd As String
Range("B2", Range("B2").End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select

11-01-2010, 11:15 AM
How about

11-01-2010, 11:33 AM
This does not work for me ..
Added a listbox instead w/Properties = sheet1!A2:B10 I do not get an error with it but I only get the list from the first column(A) not the two A&B

11-01-2010, 11:40 AM
I'm thinking of a workaround but forgot the code for concatenating columnB to columnA ... that should do it with the list box

11-01-2010, 12:15 PM
I have solved it with a second list box (Properties B2:B10) like this:

Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lRow
Cells(i, 2) = Cells(i, 1) & "_" & Cells(i, 2)
Next i
End Sub

Still need a range B2:(and last row) instead of B2:B10
Also I'm trying to concatenate in ColumnA instead of B but somehow I can't
(not terribly important for I hide both column anyhow ... but would like a correction just to learn from it)

Always ... suggestions welcome with thanks

11-01-2010, 01:01 PM
With a 2 column listbox
Me.ListBox1.List = Range("A2").CurrentRegion.Value

11-01-2010, 05:14 PM
Thank you mdmackillop
Now using two column Listbox (by changing in the properties of Listbox2 column count to 2(from 1) and the Row Source to: sheet1!MyList (having assigned a name to the range A2:B100.

Not knowing what I'm doing as usual it took me hours on Google searching "VBA two columns Userform Listbox" ...(SQL's ...ADO's .... nightmares!!!)

Am I doing something wrong here? the result appears to be good ...
Tried it you way inserting your suggestion here and there with no luck (in the initialize event ... then at the top in the code to retrieve the table from Access and others.... )
Please advise
Many thanks

Just edited th named range to : =OFFSET($A$1,0,0,COUNTA($A:$A),2)

11-03-2010, 06:24 PM
Disregarded all my previous attempts and started all over again with a Userform1 and a ListBox
(ListBox column count=3

Dim rng As Range
Dim LastRow As Long

With Worksheets("Sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set rng = .Range("A1").CurrentRegion
ListBox2.List = rng.Value
End With
... and that works like clockwork!