Run through userforms to input code

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
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,846
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,762
Office Version
2013
Platform
Windows
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,762
Office Version
2013
Platform
Windows
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,762
Office Version
2013
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,005
Messages
5,484,148
Members
407,433
Latest member
JohnSmith12345

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top