Copy Selection to several Textboxes on Userform

[spidaman]

Can anyone help correct my code please?

I'd like to copy only the first 5 values in any given selection to 5 textboxes on a userform. I've tried to create an array from selection and an array for the textboxes but I'm not sure if textboxes can be written into an array. I converted selection to a range as well as an array as I couldn't see another way of assigned value to the textboxes otherwise.

Sub Rapid_Search()

Dim txtbox(1 To 5), TargetArr As Variant
Dim SourceRng, cel As Range
Dim i, j As Long

Set SourceRng = Application.ActiveWindow.Selection
TargetArr = ActiveWorkbook.ActiveSheet.Selection.Value

txtbox(1) = frmRapid1.txtNum1
txtbox(2) = frmRapid1.txtNum2
txtbox(3) = frmRapid1.txtNum3
txtbox(4) = frmRapid1.txtNum4
txtbox(5) = frmRapid1.txtNum5

For Each cel In SourceRng
    For i = LBound(TargetArr) To (TargetArr(5))
        For j = txtbox(1) To txtbox(5)
            txtbox.Text = cel.Value    'txtbox flagged as invalid qualifier on this line
        Next
    Next
Next

End Sub

Ideally I'd like to add an If statement to handle a selected range that is more than 1 column as well as the possibility of a single cell selection.

Am struggling with this and it's a bit of a mess. Any help much appreciated.

 

[mikerickson]

TextBoxes can be put into arrays. The keyword Set has to be used to assign the values

Dim myTextBoxes(1 to 5) as MSForms.TextBox ' (or Object or Variant)
Dim SourceRng as Range, cel as Range, i as Long

Set SourceRng = Application.ActiveWindow.Selection

Set myTextBoxes(1) = UserForm1.TextBox1
Set myTextBoxes(2) = UserForm1.TextBox2
Set myTextBoxes(3) = UserForm1.TextBox3
Set myTextBoxes(4) = UserForm1.TextBox4
Set myTextBoxes(5) = UserForm1.TextBox5

For Each cel in SourceRange.Resize(5,1)
    i = i + 1
    myTextBox(i).Value = cel.Value
Next cel

Although, if the textboxes are named logically, one can use a loop like this

For i = 1 to 5
    UserForm1.Controls("TextBox" & i).Value = SourceRange.Cells(i,1)
Next i

 

[spidaman]

TextBoxes can be put into arrays. The keyword Set has to be used to assign the values

Dim myTextBoxes(1 to 5) as MSForms.TextBox ' (or Object or Variant)
Dim SourceRng as Range, cel as Range, i as Long

Set SourceRng = Application.ActiveWindow.Selection

Set myTextBoxes(1) = UserForm1.TextBox1
Set myTextBoxes(2) = UserForm1.TextBox2
Set myTextBoxes(3) = UserForm1.TextBox3
Set myTextBoxes(4) = UserForm1.TextBox4
Set myTextBoxes(5) = UserForm1.TextBox5

For Each cel in SourceRange.Resize(5,1)
    i = i + 1
    myTextBox(i).Value = cel.Value
Next cel

Although, if the textboxes are named logically, one can use a loop like this

For i = 1 to 5
    UserForm1.Controls("TextBox" & i).Value = SourceRange.Cells(i,1)
Next i

Thanks very much mikerickson, I'll take a look tomorrow and get back to you

 

[spidaman]

@mikerickson thanks for your help with bringing the selected worksheet data into the userform textboxes. This is close to what I'm trying to achieve, but the resize method always copies values within 5 rows and 1 column of the top selected cell. This is great for when the selection is more than 5 rows as it populates the first 5 rows only, however if the selection is less than 5 rows it still copies the selection plus 2 more rows from the cells below regardless. I need the textboxes to be populated with selected cell values only so if 3 cells are selected, only three textboxes are populated, if 1 cell is selected only 1 textbox is populated and so on.

I've tried to elaborate with the code below but am getting a type mismatch on the Rows function that I added:

Public Sub RSearch()

Dim txtboxes(1 To 5) As MSForms.TextBox
Dim SourceRng, cel As Range
Dim i As Long

Set SourceRng = Application.ActiveWindow.Selection

Set txtboxes(1) = frmRapid1.txtNum1
Set txtboxes(2) = frmRapid1.txtNum2
Set txtboxes(3) = frmRapid1.txtNum3
Set txtboxes(4) = frmRapid1.txtNum4
Set txtboxes(5) = frmRapid1.txtNum5

If SourceRng.Columns > 1 Then  ' the columns function here doesn't work
    MsgBox "Select a single column only"
    End
ElseIf SourceRng.Rows < 5 Then   ' the rows function here doesn't work
    For Each cel In SourceRng
        i = i + 1
        txtboxes(i).Value = cel.Value
    Next cel
Else
    For Each cel In SourceRng.Resize(5, 1)
        i = i + 1
        txtboxes(i).Value = cel.Value
    Next cel
End If

End Sub

I have also tried to add msgbox handling for selections of more than 1 column.

Please can you take a look and see what you think?

 

[Norie]

You should be checking SourceRng.Rows.Count.

 

[spidaman]

You should be checking SourceRng.Rows.Count.

Thanks Norie that's sorted it!