vba 在 Textbox1 中显示选定的单元格范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13121413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display selected range of cells in Textbox1
提问by user1782817
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:
我在尝试将选定范围的单元格从 Excel 显示到用户窗体文本框时遇到问题，例如C1:E14.下面的代码给了我一个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 .Valueit's giving the above mentioned error, but if I use .Select "True"it is printing in the UserForm text box.
如果我使用.Value它会出现上述错误，但如果我使用.Select "True"它会在用户窗体文本框中打印。
回答by Brad
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"))
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)
