Load Userform from Array using a Loop

[Ryan H]

I have a column with a values in it. I want to load my userform (frmPF) with
these values with the code below, but my textboxes, comboboxes are empty when
the userform shows, why?

Sub LoadUserform()

Dim ctrlArray As Variant
Dim i As Long
Dim wksItem As Worksheet
Dim n As Long

' set worksheet to recall userform data
Set wksItem = Sheets("Data Storage")

' find column number of reference number
n = WorksheetFunction.Match(Target, wksItem.Rows("1:1"), 0)

' array of control values
With frmPF
ctrlArray = Array(.TextBox1, .TextBox1, .TextBox1, .TextBox1,
..ComboBox1)
End With

For i = LBound(ctrlArray) To UBound(ctrlArray)
ctrlArray(i) = wksItem.Cells(i, n)
Next i

frmPF.Show

End Sub

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Option Base 1
Sub LoadUserform()

Dim ctrlArray As Variant
Dim i As Long
Dim wksItem As Worksheet
Dim n As Long
Dim Target As Range 'I needed something to test.

' set worksheet to recall userform data
Set wksItem = Sheets("Data Storage")
Set Target = wksItem.Range("d1")

' find column number of reference number
n = WorksheetFunction.Match(Target, wksItem.Rows("1:1"), 0)

'I'd load the form first. I think it serves as better documentation
'but it isn't necessary (from what I could tell)
'Load frmPF

' array of control values
With frmPF
ctrlArray = Array(.TextBox1, .TextBox2, .TextBox3, _
.TextBox4, .ComboBox1)
End With

For i = LBound(ctrlArray) To UBound(ctrlArray)
ctrlArray(i).Value = wksItem.Cells(i, n)
Next i

frmPF.Show

End Sub

I did add "option base 1" to make sure that the lower bound of ctrlarray was a
valid row number for .cells(i,n).

And I changed the ctrlArray to use .textbox1 through .textbox4 (I figured 4
..textbox1's were a typo).

But the biggest thing I changed was adding the .value property.

 

[Matthew Herbert]

Ryan,

What does "Target" refer to? What does "n" return? (Is "Target" even
located within the first row of the worksheet?) Is your wksItem.Cells(i, n)
evaluating as wksItem.Cells(0, n)? Do you really want to refer to .TextBox1
four different times within your Array? Start here and see if one of these
questions points you to an answer. If not, repost the results of these
questions with any further/new problems as they relate to loading your user
form.

Best,

Matthew Herbert

 

[Ryan H]

Sorry for being so vague Matthew.

Target is a variable String. Its a reference number that is found in the
"Data Storage" worksheet.

I have Option Base 1 at the very top of the module so the LBound of the
Array will be 1.

The ctrlArray should look like this.
ctrlArray = Array(.TextBox1, .TextBox2, .TextBox3, .TextBox4, .ComboBox1)

Basically this is what I looking to do:

Find the column "Target" (Reference Number) is in. The column contains
values for all my controls on the userform frmPF. Then scan down that column
giving my controls in my array values from that column. For example, say n =
Column 2, then

frmPF.Textbox1 = Sheets("Data Storage").Cells(1, 2)
frmPF.Textbox2 = Sheets("Data Storage").Cells(2, 2)
frmPF.Textbox3 = Sheets("Data Storage").Cells(3, 2)
frmPF.Textbox4 = Sheets("Data Storage").Cells(4, 2)
frmPF.ComboBox1 = Sheets("Data Storage").Cells(5, 2)

 

[Matthew Herbert]

Ryan,

Create another variable, e.g. Dim Ctrl As Control, and add the following
lines in your For Next loop:

For i = LBound(ctrlArray) To UBound(ctrlArray)
Set Ctrl = ctrlArray(i)
Ctrl.Value = wksItem.Cells(i, n)
Next i

See if this will work for you.

Best,

Matt