Attempting to Use Set Statement to loop through Textboxes on UserForm

Question

This funding model was originally written in Lotus and has been migrated again and again for several years.  Instead of having any For Loops, they have set up this program so each month has it's own procedures that are identical and written 12 separate times.  Memory is being eaten away and I'm trying to be a little more efficient by writing 3 procedures with For/Next loops to get rid of code that has been replicated 36 times.  I have hit a snag with using the Set statement.

 I am trying to read some values in textboxes on a Userform.  I don't want every textbox only the ones where the names are appended with numbers 1-12--those textboxes contain necessary data.  Once the loop is complete, I should have an array filled with 12 pieces of financial data.  If I wanted to get fancier, I would like to make a 3 dimensional array that contains all the data for all the months.

I have gotten  Object variable not Set statments, type mismatch errors on Set statments that I cannot reason through.  Here is the first of three loops:

    myCount = 1
 Set ctrl = PPForm.Control  'initially I had this control as Me.control on both lines and got a Data Member Not Found Error.  VB doesn't like this either  

For Each ctrl In PPForm.Control
        If ctrl = IsObject(TextBox) Then
            If Not (IsNumeric(Right(ctrl.Name, 1))) Then Isctrl = False
            End If
                If IsNumeric(Right(ctrl.Name, 2)) Or IsNumeric(Right(ctrl.Name, 1)) Then
                    Isctrl = True
                    For myCount = 1 To 12
                    ArrDollarEstimate(myCount) = ctrl.Value
                    Next myCount
                End If
        Next ctrl

Is it necessary to use the set statment?  Is there a better way to read the text data off the UserForm?

---

tracy

Answer 1

This looks like VB6 code...

Please verify if you are using VB6 or VB.Net as VB6 is not supported here.

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 2

This is VBA code in Excel 2010

---

tracy

Answer 3

Hi Tracy,

We have a VBA forum that mainly talking about VBA issue. In order to provide better support, I’ll move your thread to Visual Basic for Applications (VBA) forum.

Thank you.

---

Shanks Zen
MSDN Community Support | Feedback to us

Answer 4

Assuming the default textbox names of Textbox1 to Textbox12 then the following will read those textboxes in order and compile a simple array of the values separated by commas when you click CommandButton1. The example simply displays the string in a messagebox

Option Explicit
Dim ctrl As Control
Dim myCount As Long
Dim lngBoxNum As Long
Dim ArrDollarEstimate As Variant
Private Sub CommandButton1_Click()
For Each ctrl In Me.Controls
    If IsNumeric(Right(ctrl.Name, Len(ctrl.Name) - 7)) Then
        lngBoxNum = Right(ctrl.Name, Len(ctrl.Name) - 7)
        For myCount = 1 To 12
            If myCount = lngBoxNum Then
                If myCount = 1 Then
                    ArrDollarEstimate = ctrl.Value
                Else
                    ArrDollarEstimate = ArrDollarEstimate & "," & ctrl.Value
                End If
            End If
        Next myCount
    End If
Next ctrl
MsgBox ArrDollarEstimate
Unload Me
End Sub

---

Graham Mayor - Word MVP
www.gmayor.com

Answer 5

Thank you for your suggestion.  Thank you for the function correction you suggest but my question has more to do with the Set statment for the variables that are objects.  I was getting the error Block variable not Set and can't get past it--this is what I am most concerned about.

You also bring up an interesting point as you have taken my array and made it a variant.  Is there a reason to do this instead of just declaring the array variable with bounds?

Thanks!

---

tracy

Answer 6

The error message was telling you that there is no member of the userform called Control. If you get into the habit of declaring your variables and forcing that with Option Explicit as the start of the module - see http://www.gmayor.com/installing_macro.htm  this would have been obvious when you entered it.

As you are looping through the controls in the form

For Each ctrl In ppForm.Controls

there is no need to set ctrlto a value before the loop as it will be set for each member of the controls set in the loop. However if you want to set it it to a particular control for some reason then you have to set it to the particular control index and the syntax for that would be (say)

Set ctrl = ppForm.Controls(1)

Similarly setting the value of myCount to 1 before running a loop

For myCount = 1 To 12

which is going to set it sequentially to something different, is also superfluous.

As for the array - the code I suggested was posted without any idea of what you were ultimately trying to achieve. Maybe the following will help clarify?

Option Explicit
Private ctrl As Control
Private myCount As Long
Private lngBoxNum As Long
Private strNums As String
Private ArrDollarEstimate As Variant
Private Sub CommandButton1_Click()

For Each ctrl In Me.Controls
    If IsNumeric(Right(ctrl.Name, Len(ctrl.Name) - 7)) Then
        lngBoxNum = Right(ctrl.Name, Len(ctrl.Name) - 7)
        For myCount = 1 To 12
            If myCount = lngBoxNum Then
                If myCount = 1 Then
                    strNums = ctrl.Value
                Else
                    strNums = strNums & "," & ctrl.Value
                End If
            End If
        Next myCount
    End If
Next ctrl
MsgBox strNums
ArrDollarEstimate = Split(strNums, ",")
For myCount = 0 To UBound(ArrDollarEstimate)
    MsgBox ArrDollarEstimate(myCount)
Next myCount
Me.Hide
End Sub

---

Graham Mayor - Word MVP
www.gmayor.com

Answer 7

You cannot get the value in the textbox using ctrl.Value.  Do you have another suggestion?

---

tracy

Answer 8

There was an errant line in the code that somehow managed to be included when I pasted it. I have corrected the error in my previous response. This is as close as I can get from the information you have provided and ctrl.Value works here.

I would need to see the form itself to check it further.

---

Graham Mayor - Word MVP
www.gmayor.com