Run through userforms to input code

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,858
Office Version
2010, 2007
Platform
Windows
Hello Armghan Haider,

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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,420
Office Version
2016
Platform
Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,070
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

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

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,070
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,070
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:

Forum statistics

Threads
1,078,214
Messages
5,338,899
Members
399,267
Latest member
Danielle1017

Some videos you may like

This Week's Hot Topics

Top