0

I am facing an issue while trying to display a selected range of cells from Excel to a UserForm textbox, say C1:E14. The code below is giving me an Runtime error '13' Type Mismatch: 

Private Sub CommandButton1_Click()
Dim ActSheet As Worksheet
Dim SelRange As Range
Set ActSheet = ActiveSheet
Set SelRange = Selection
Range("TABLE").Select
Application.Goto "TABLE"
UserForm1.TextBox1.Text = Range("C1:E14").Value
'UserForm1.TextBox1.Text = Range("C1:E14").Select
End Sub

If I use .Value it's giving the above mentioned error, but if I use .Select "True" it is printing in the UserForm text box.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user1782817
  • 1
  • 1
  • 1
  • 1
  • 4
    That's a range of 42 individual cells ... what do you want in the text box? all of them? delimited/displayed how? – Alex K. Oct 29 '12 at 12:22
  • 1
    A similar question and discussion from the past: http://stackoverflow.com/questions/5590963/get-text-from-a-cell-range-in-excel-vba – Jüri Ruut Oct 29 '12 at 12:48
  • 1
    To sum it up, you cannot use the `Value` (or `Text`) property of a `Range` object that contains more than one cell, unless you assign it to a variable of type `Variant` (because it's an array). – Olle Sjögren Oct 29 '12 at 13:09
  • Why do you need to do this? Just out of curiosity. – Stepan1010 Oct 29 '12 at 15:13

1 Answers1

0

The comments are correct. You are trying to set a range to a string.

you can turn it into a range using something like this

Public Function Join(seperator As String, rng As Variant) As String

    Dim cell As Variant
    Dim joinedString As String
    For Each cell In rng
        joinedString = joinedString & cell & seperator
    Next cell
    joinedString = Left(joinedString, Len(joinedString) - Len(seperator))
    Join = joinedString

End Function

Then

UserForm1.TextBox1.Text = Join(",",Range("C1:E14"))

or if you want to make new lines out of each cell

Dim joinedString as string
joinedString  = Join("|",Range("C1:E14"))
UserForm1.TextBox1.Text = Replace(joinedString  , "|", vbCrLf)
Brad
  • 11,934
  • 4
  • 45
  • 73
  • In versions 2019+/MSExcel365 this can be replaced by e.g. `Me.TextBox1.Text = Application.WorksheetFunction.Textjoin(vbNewline,False,Sheet1.Range("C1:E14"))` – T.M. Dec 03 '21 at 19:26