VBA Excel UserForm Simple Question

[Saladsamurai]

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

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?

 

[CFDFEAGURU]

Hello,

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.

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)
Next
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.

Thanks
Matt

 

[Saladsamurai]

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

PowerArray(i)=Userform.Powerbox(i)

 

[CFDFEAGURU]

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.

Thanks
Matt

 

[Saladsamurai]

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.

 

[Saladsamurai]

Hello,

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.

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)
Next
End Sub

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).

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 :

Sub Test ()
PowerForm.Show
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?

 

[Saladsamurai]

Okay this almost works:

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.

 

[CFDFEAGURU]

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.

Yes, I got the same until I dimensioned the array as (3,0).

I guess I could just Cut Off the 2 extra terms and ReDim the array.

That is exactly what I was going to do.

Thanks
Matt

 

[Saladsamurai]

Yes, I got the same until I dimensioned the array as (3,0).



Thanks
Matt

hmmm... I wonder why redimming it as 2D would fix that?