Newbie to userforms question here.

Setting up a user UserForm for Data entry there are
32 TextBoxes named CAT1BOX, CAT2BOX, CAT3BOX,... CAT32BOX
28 Labels named CAT1, CAT2, CAT3, ... CAT28

I am trying to name and populate them dynamically using the For-Next
statement below, but am getting a RunTime error '91 "Object variable
or With block variable not set"

Private Sub UserForm_Initialize()
Dim MyField As Object, MyWork As Object

Me.Caption = "WorkFlow Tracking for " &
Sheets("Data").Range("B4").Value & _
" For " & Sheets("Data").Range("B3").Value

For i = 1 To 32
If i < 29 Then
MyField = "CAT" & i
MyField.Text = Sheets("data").Range("J17").Offset(-i + 1,
0).Value
MyWork = "CAT" & i & "BOX"
MyWork.Value = Sheets("data").Range("B12").Offset(0, i -
1).Value
Else
MyWork = "CAT" & i & "BOX"
MyWork.Value = Sheets("data").Range("B12").Offset(0, i -
1).Value
End If
Next
End Sub

I know I am not seeing something incrediblly stupid... If anyone knows
of an "Idiots guide to using UserForms in Excel" ...

Just to loop differently (you can change it back!)

for i = 1 to 28
me.controls("cat" & i).caption _
= Sheets("data").Range("J17").Offset(-i + 1, 0).Value

me.controls("Cat" & i & "box").value _
MyWork.Value = Sheets("data").Range("B12").Offset(0, i - 1).Value
next i

for i = 29 to 32
me.controls("Cat" & i & "box").value _
MyWork.Value = Sheets("data").Range("B12").Offset(0, i - 1).Value
next i





wrote:

Newbie to userforms question here.

Setting up a user UserForm for Data entry there are
32 TextBoxes named CAT1BOX, CAT2BOX, CAT3BOX,... CAT32BOX
28 Labels named CAT1, CAT2, CAT3, ... CAT28

I am trying to name and populate them dynamically using the For-Next
statement below, but am getting a RunTime error '91 "Object variable
or With block variable not set"

Private Sub UserForm_Initialize()
Dim MyField As Object, MyWork As Object

Me.Caption = "WorkFlow Tracking for " &
Sheets("Data").Range("B4").Value & _
" For " & Sheets("Data").Range("B3").Value

For i = 1 To 32
If i < 29 Then
MyField = "CAT" & i
MyField.Text = Sheets("data").Range("J17").Offset(-i + 1,
0).Value
MyWork = "CAT" & i & "BOX"
MyWork.Value = Sheets("data").Range("B12").Offset(0, i -
1).Value
Else
MyWork = "CAT" & i & "BOX"
MyWork.Value = Sheets("data").Range("B12").Offset(0, i -
1).Value
End If
Next
End Sub

I know I am not seeing something incrediblly stupid... If anyone knows
of an "Idiots guide to using UserForms in Excel" ...

--

Dave Peterson

Thank you, that worked. Well it did as soon as I rememberd that down
is negative and changed the "-i +1" to "i-1" so that I wasn't trying
to read cells located about six inches above my computer... :)



On Jul 26, 7:41 am, Dave Peterson wrote:
Just to loop differently (you can change it back!)


Dave Peterson- Hide quoted text -

- Show quoted text -

Glad you got it working. And found the error. I didn't notice that portion of
your code.

wrote:

Thank you, that worked. Well it did as soon as I rememberd that down
is negative and changed the "-i +1" to "i-1" so that I wasn't trying
to read cells located about six inches above my computer... :)

On Jul 26, 7:41 am, Dave Peterson wrote:
Just to loop differently (you can change it back!)


Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson