Run through userforms to input code

Armghan Haider

New Member
Joined
Jun 28, 2018
Messages
8
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hello Armghan Haider,

I'll send you my email address in a private message (PM).
 
Upvote 0
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 :
Code:
Sub ShowUserForms()
    Dim i As Long
    Const NumberOfForms = 16
    
    For i = 1 To NumberOfForms
        UserForms.Add("UserForm" & i).Show
    Next
End Sub
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :
Code:
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
 
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top