none
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

    • Moved by Shanks Zen Monday, March 26, 2012 5:36 AM Not VB issue (From:Visual Basic Language)
    Thursday, March 22, 2012 8:08 PM

Answers

  • 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


    Tuesday, March 27, 2012 6:54 AM

All replies

  • 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"

    Thursday, March 22, 2012 8:51 PM
  • This is VBA code in Excel 2010

    tracy

    Sunday, March 25, 2012 8:05 PM
  • 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

    Monday, March 26, 2012 5:36 AM
  • 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

    Monday, March 26, 2012 2:35 PM
  • 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

    Monday, March 26, 2012 5:57 PM
  • 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


    Tuesday, March 27, 2012 6:54 AM
  • You cannot get the value in the textbox using ctrl.Value.  Do you have another suggestion?

    tracy

    Monday, April 02, 2012 6:36 PM
  • 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

    Tuesday, April 03, 2012 2:54 PM