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)

--
Cheers,
Ryan


"Matthew Herbert" wrote:

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" wrote:

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

--
Cheers,
Ryan

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
"Ryan H" wrote:

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)

--
Cheers,
Ryan


"Matthew Herbert" wrote:

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" wrote:

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

--
Cheers,
Ryan