Run through userforms to input code

[Armghan Haider]

Hi

I have 16 Userforms. Labeled as Userform1, Userform2.......Userform15, Userform16.

I have prompt text boxes in each of these User forms.

I need a for a loop that will allow me to run through all these forms allowing me to input code in each form.

I was thinking

For index As Integer = 1 To 16
Userform index.ShowNext index

But this doesnt work cuz I get error saying that Userform is not defined.

How can I get around this?
Thanks

 

[Armghan Haider]

Hey what’s your email Leith?
Thanks!

 

[Leith Ross]

Hello Armghan Haider,

I'll send you my email address in a private message (PM).

 

[Jaafar Tribak]

Since all your 16 userforms have a similar name in the form of "UserFormX", I would consider not using the VBExtensibility library which requires Access to the VBProject to be trusted.

This should yield the same result with less code and complexity :

Sub ShowUserForms()
    Dim i As Long
    Const NumberOfForms = 16
    
    For i = 1 To NumberOfForms
        UserForms.Add("UserForm" & i).Show
    Next
End Sub

 

[My Aswer Is This]

I see the original poster is now sharing data through private messages and Email.

So has this question now been answered completely?

I have been sitting on the sidelines watching this since my last posting to see the answer.

I still believe needing 16 UserForm to do such a simple task is not the way to do things.

Since you say your new to Vba I think you could learn from saying. Here is what I'm trying to do can someone show me the way I should do this.

But for some reason you have decided the best way is to use 16 UserForms and now want us to help you do it this way.

I would like to see the final answer if there is one.

 

[Armghan Haider]

Hey, as I said before, I am sure there are better ways to do it rather than using 16 forms. I never said you have to use 16 forms to solve this. I am open to anyway that works. If you find a way to make this code easier please feel free to do so. Keith is kindly looking at it for me at the moment. I will update the forum with the info/advice he provides.

Thanks

 

[Armghan Haider]

.

 

[Armghan Haider]

Since all your 16 userforms have a similar name in the form of "UserFormX", I would consider not using the VBExtensibility library which requires Access to the VBProject to be trusted.

This should yield the same result with less code and complexity :

Sub ShowUserForms()
    Dim i As Long
    Const NumberOfForms = 16
    
    For i = 1 To NumberOfForms
        UserForms.Add("UserForm" & i).Show
    Next
End Sub

This was helpful!
Thanks

 

[My Aswer Is This]

Hey, as I said before, I am sure there are better ways to do it rather than using 16 forms. I never said you have to use 16 forms to solve this. I am open to anyway that works. If you find a way to make this code easier please feel free to do so. Keith is kindly looking at it for me at the moment. I will update the forum with the info/advice he provides.

Thanks

Please explain again in detail what your ultimate goal is here

Do not tell us how you want it done. Just tell us what your ultimate goal is.
If I remember you want to load about 32 cells on your work sheet with data

 

[My Aswer Is This]

Since I assume one reason you thought of 16 UserForms was best is because you did not want to put 16x3 Textboxs on your Userform. That would make 48 Textboxes which would fill up your Form.

Here is a solution I have.

You need:

One Userform
One ListBox named ListBox1

And three Textboxes Name TextBox1 TextBox3 and TextBox3


Now when you open the User form your listbox will be loaded with the value 3
Which is the first row you want to populate

Now enter your data into the three TextBox's and then click on the number 3 in the listbox

Then you script runs filling in Row 3 column C D and E

Now the listbox has the value 4 entered the three textboxs are cleared and ready for your next set of entries

So to do this to fill in 16 rows you will have to do this 16 times. But with only one UserForm.

It you were to use maybe 6 Textboxs then we would only need to do this 8 times.

But then I would have to modify this script.


Put this script in Your Userform:

Private Sub ListBox1_Click()
'Modified 7/5/18 5:15 AM EDT
Dim ans As Long
ans = ListBox1.Value
Cells(ans, "C").Value = TextBox1.Value
Cells(ans, "D").Value = TextBox2.Value
Cells(ans, "E").Value = TextBox3.Value
ListBox1.Clear
ListBox1.AddItem ans + 1
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox1.SetFocus
End Sub
Private Sub UserForm_Initialize()
ListBox1.AddItem "3"
End Sub