PDA

View Full Version : Solved: textbox value = cell range



ndendrinos
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?

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

ndendrinos
11-01-2010, 10:36 AM
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
11-01-2010, 11:15 AM
How about
Range("A2").CurrentRegion

ndendrinos
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

ndendrinos
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

ndendrinos
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

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

ndendrinos
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)

ndendrinos
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!