Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

VBA Excel UserForm Simple Question

  1. Sep 4, 2009 #1
    :smile: Hello there!

    I have created a Useform with a few rows of TextBoxes.

    I have Changed the names of them all to "Powerbox1" , "Powerbox2" ....etc.

    Now in my code, I would like to store all of those values taken from the UserForm and store them in an array called Power()

    I have been trying things like

    Code (Text):
    Private Sub OkayButton_Click()

        Dim Power(48) As Single
        Dim i As Integer
        For i = 1 To 48
            Power(i) = PowerForm.Powerbox(i).Text
        Next i
        MsgBox Power(1)

    End Sub
    But this method does not work.

    Is there anyway to assign values to this array without doing them individually?
  2. jcsd
  3. Sep 4, 2009 #2

    Here is an example. I just used three text boxes. You have to make sure it doesn't pick up the value for the command button.

    Code (Text):
    Private Sub CommandButton1_Click()

    Dim i As Integer
    Dim test(3, 0) As Integer

    For i = 0 To 3
        test(i, 0) = UserForm1.Controls(i)
        MsgBox test(i, 0)
    End Sub
    If you enter an integer in each text box the message box will display each value as you iterate through the loop. You will also get a value of zero on the last iteration of the loop. I believe that is the value of the command button.

    Hope this helps.

  4. Sep 5, 2009 #3
    I will try this when I get home. Thanks!

    What surprises me, though, is that you can't affix the particular
    name of the text boxes.

    For example: what if the form has boxes for 'power' & 'flowrate'
    and I want to store all power values in one array and all flows
    in another?

    It would seem that I could just name the textboxes
    'powerbox1 ...' and 'flowbox1...'

    and then use something like

  5. Sep 5, 2009 #4
    In Excel there is no support for the .ControlsType command. This command is supported in Access. I use it frequently in Access to loop through a form or subform that contains a group of checkboxes and to ask the user to enter information depending on what checkbox is selected.

    Your thinking is logical but Excel isn't.

  6. Sep 5, 2009 #5
    So I guess the solution to this would be to use separate
    forms for each parameter and then pass the values
    between the different codes.
  7. Sep 5, 2009 #6
    Hmmm... I seem to be having some issues with this code.

    I simply modified it to be a 1-D Array. And I used Option Base 1 (i.e. the first element of array is index=1 as opposed to zero).

    Code (Text):
    Option Explicit
    Option Base 1

    Private Sub OKButton_Click()
        Dim i As Integer
        Dim Test(3) As Integer
        For i = 1 To 3
            Test(i) = PowerForm.Controls(i)
            MsgBox Test(i)
        Next i

    End Sub
    I also have a sub that is simply :

    Code (Text):
    Sub Test ()
    End Sub
    When I run the latter, my UserForm "PowerForm" pops up. I enter 1, 2, 3 into the first 3 Textboxes TextBox1 , TextBox2 , TextBox3 , respectively.

    The Message boxes start to pop up: It does not show "1"......only "2" "3" and then a friggin' "Runtime Error '13' : Type MisMatch" shows up.

    I am not sure what to make of this? Any ideas?
  8. Sep 5, 2009 #7
    Okay this almost works:

    Code (Text):
    Option Explicit
    Option Base 1

    Private Sub OKButton_Click()
        Dim i As Integer
        Dim Test(12) As Variant
        Dim TextBox As Control
        i = 1
        For Each TextBox In Me.Controls
            Test(i) = TextBox
            MsgBox Test(i)
            i = i + 1
        Next TextBox

    End Sub
    The problem is this: My Test() array is only dimmed to 12 (i.e. the # of textboxes). However, the # of controls is 14----> 12 TextBoxes + OK_Button + Cancel_Button

    So I am getting a subscript out of Range error because it is a stupid For Loop.

    Stupid in the sense that is ambiguous "For Each .." does not allow me to set the bounds of the For Loop.

    I guess I could just Cut Off the 2 extra terms and ReDim the array.
  9. Sep 6, 2009 #8
    Yes, I got the same until I dimensioned the array as (3,0).

    That is exactly what I was going to do.

  10. Sep 6, 2009 #9
    hmmm... I wonder why redimming it as 2D would fix that?
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook