Userform Macro to Use Textbox input to Generate blank rows.

I have a userform with 16 Textboxes Named "Text1" through "Text16". What I am trying to do is apply code that if there is content in the textbox it generates a new row after row15 and inputs the text in cellA of that row. Ex. "Text1" = JOE "Text2" = TONY......Hit Commandbutton1 on userform. It inserts Row16 puts "JOE" in A16.....then sequentially it inserts Row17 puts "TONY" in A17.

Originally Posted by Naveed Raza

Hi Jared

try this attached file and let me know

Naveed,

Thank you for the reply. It is very close to the desired output but as I think it will be hard to get the issues across through text I have instead attached an example file of my workbook. To get to the Userform of concern: Use the Commandbutton on the first worksheet, Select the commandbutton on that Userform that states "Build New Template", Hit Submit on the "Audit Information" userform no need to fill anything in, The subsequent Userform (Useform2) titled "Audit Categories" is the one of concern. Type a number in the first Textbox and it will populate with a certain number of textboxes. If you look the "Textboxes" that appear beside the "Category" labels are the one of concern. There are up to 16 categories (i.e. Textboxes)for the user to input. For each of those textboxes that a category name is filled in for I am looking that a Row is generated below row 15 (in sequential order) and the Text is tranlated into the generated row. Just take a look at the file and get back with questions.

Forum Example.xlsm

Originally Posted by HaHoBe

Hi, jaredmccullough,

I sort of dislike to havbe a large number of Textboxes for running a job like this, IŽd go with 1 TextBox and use a separator there like | to split the contents.

Please Login or Register  to view this content.

Ciao,
Holger

Holger thank you for the reply see response above with workbook and instructions to get to userform. I think originally the desired output may not have been understood.

Jared

Holger,

Thank you for the reply. I like the changes you have made to the naming of the first sheet as it will eliminate having the same "New Template" issues. I think there maybe some confusion on the second Submitform2 output. The code I originally had where it added the cells to A7:A14 and D7:D14 was intended to be included with the "Adding Row" Function. The "Adding Rows" portion you put in worked partially as desired. Instead of it moving over and adding values in in columnD after the 8th textbox I was looking for it to continue to just add rows for all 16 textboxes. Desired Output: Add 16 categories to appropriate cells from A7:A14 and D7:D14 as well as add a row for each. If you look at the newly updated code I have changed it around the only issue now is that category 9 and on goes to cell D16 and up. So basically what you have now was set up in the previous code to go in A7:A14 and D7:D14 what you have added works till "Category8" but from Category9 on is supposed to continue to add rows. I know that is kinda of confusing but I hope you will see in the workbook. Bascially instead of adding in D16 and on for Textboxes "Text9" through "Text16" i want them to continue to add rows in columnA.

Forum Example.xlsm

Holger,

See active Worksheet ARG-130829. If you see A7 = Application and Row15 = Application. This is what I am trying to achieve for all 16 categories. My next goal will then be to create some hyperlink that when the user clicks cell A7 "Application" it will take them to them to Row15. This is important because category16 may be 100s of cells down so my intent would be the click D14 where "Category16" will be and it will take them to the Row where "Category16" was placed.

I forgot to mention some important information. What I am meaning is that there is a freeze pane after Row15 on the worksheet. What my desired outcome would be is that when the user selects the category from cell that the textbox is inserted in (The first part of the code). The macro will bring (Scroll) that matching header that was created to the meet the freeze pane. The point of this is that the user can pull up the desired category without having to scroll through what could be a large quantity of cells.

Holger,

If you view the example Workbook I have attached and look at the WS titled "ARG-130829" you can maybe understand the concept a little better. If you see the gray cells A7:A14 and D7:D14 they are match the header rows below Row 15. What I am trying to do is create a macro that say when the user clicks on A10 "Process Hazard Analysis" it brings Row54 up to the top of the Freeze Pane. How the rows are generated are through Userform2. The user inputs a heading using the "Text1" through "Text16" textboxes and a number of questions using "Question1" through "Question 16" textboxes.

The headings (Text1-Text16) generate a Row and the # of Question (Question1-Question16) generate a number of rows between the headings and use the category number to generate a numbering system (i.e. 1.001, 1.002, 2.002, etc....)

Say the user puts in Text1 = JON and Question1 = 4 and Text2 = JIM and Question2 = 8 that would mean that Row16 = Jon, Row17-20 would be 1.001 through 1.004, Row21 would be JIM and Row22-28 would be 2.002 through 2.008

***********************

Originally Posted by HaHoBe

Hi, Jared,


Holger

Holger,

Formatting and stuff works well. Only issue is if user forgets to put in Question Number it comes up with a "Run-Time Error 13" Type Mismatch on line

lngMax = Controls("Question" & lngCounter).Value

Any clue as to why (this basically negates the MSG because it does not reach that far)

Again thanks for the help.

Jared

Originally Posted by HaHoBe

Hi, Jared,

maybe adjust the design by yourself in filling the Textboxes with 0 in designer mode or only allow the code to run when the corresponding textboxes are both filled? This is a queation of design in my view not only of coding.

Instead of

Please Login or Register  to view this content.

maybe use

Please Login or Register  to view this content.

Ciao,
Holger

Holger,

Thanks for the suggestions and everything seems to work as intended. There is 1 function with this I am still trying to achieve. I have a "Freeze Pane" on Row15 so that row 15 and above remain locked. If you will view the following code at the end what is happening is that your "Insert Rows" function is working at the same time as a categories section is being populated. (Categories section is A8:A15 and D8:D15) Where "Text1" is going to A8 and sequentially from there until "Text16" is going to D15. What I was trying to create is either some kind of link or else worksheet code that when the user clicks the category from the Category Section (as defined earlier) that the header matching this category will scroll to the top (Meaning against the freeze pane).