Copy Selection to several Textboxes on Userform

spidaman

Board Regular
Joined
Jul 26, 2015
Messages
114
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
TextBoxes can be put into arrays. The keyword Set has to be used to assign the values

VBA Code:
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

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

spidaman

Board Regular
Joined
Jul 26, 2015
Messages
114
Office Version
  1. 365
Platform
  1. Windows
TextBoxes can be put into arrays. The keyword Set has to be used to assign the values

VBA Code:
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

VBA Code:
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

Board Regular
Joined
Jul 26, 2015
Messages
114
Office Version
  1. 365
Platform
  1. Windows
@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:

VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
You should be checking SourceRng.Rows.Count.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,994
Messages
5,628,029
Members
416,287
Latest member
wanji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top