Solved: textbox value = cell range

[ndendrinos]

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?

[mdmackillop]

Range("A2:B") is not a valid range

[ndendrinos]

Thank you and you are correct . The code should read

Range("A2:B50")

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

[mdmackillop]

How about
Range("A2").CurrentRegion

[ndendrinos]

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

[ndendrinos]

I'm thinking of a workaround but forgot the code for concatenating columnB to columnA ... that should do it with the list box

[ndendrinos]

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 B2and 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

[mdmackillop]

With a 2 column listbox
[VBA]Me.ListBox1.List = Range("A2").CurrentRegion.Value
[/VBA]

[ndendrinos]

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)

[ndendrinos]

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!