VBA for button to add specific amount of rows with exceptions

[SuperOnlineGuy]

I am trying to improve some code in a workbook that allows users to add a specific number of rows based on their input after pressing a button. The code is very basic and I have been tasked to improve it (though my VBA skills, or lack thereof, are as basic as the code).

What is currently happening:
Users will click a button that prompts them to input how many rows they'd like to add. This is a numerical value. The code will then copy two hidden rows containing cell formats and formulas, and paste those hidden rows based on the value they entered at the prompt. Right now the lower hidden row is copy / pasted first, then the top row is copy / pasted to avoid overwriting rows.

What the problem is:
If a user makes a mistake on how many rows they want entered, they cannot undo or backup. For example: A user enters 3 into the prompt. A total of 6 rows will be added. The user then realizes they actually needed 5 rows, so they click the button, enter 2, and suddenly the entire sheet disrupted due to the code. Incorrect cells are suddenly copied and it destroys the formatting in place, causing the user to start from scratch if they haven't saved prior to entering data.

What I'd like to happen:

• User clicks button to add specific number of rows for top section via prompt. User clicks another button to add specific number of rows for bottom section via prompt.
• If user makes a mistake in number of rows entered in prompt, they can add or remove rows by clicking button again and entering correct number.
• If no data is present in created rows yet, entering another value into the prompt will "wash away" the old rows and input the correct number of rows.
• If data is present in created rows, the new value entered into prompt will produce these new rows following the rows with data entered.

Attached is a very watered down version of the template being used to avoid private info. The AddThese button is currently used to add both rows, it is the original coding. I plan on recreating these sheets from scratch to avoid using merged cells and other strange formatting going on, to simplify everything.

Considerations / suggestions for improvement and ease of use?

• Are buttons the way to go about this task?
• Would a cell where user enters the variable, then clicks a button to run macro and insert rows based on cell value, be easier to code / understand for user?
• Is it necessary to hide the cells I want the formatting to use or is there a way to tell excel what should be added? Does it matter?

[mehmetcik]

Hi

It seems pretty easy.

You only one Macro on the Sample but two buttons. Why?

I would approach this Slightly differently.

1. Get rid of the buttons. Clicking in Column A can run all the macros that you might Need.

2. I would not hide Rows 7 and 11. there is no need. Let your user enter the data in those rows.

3. If the User needs more rows click on Column A.

Macro Offers Option to either insert new rows at the right place or delete and spare rows.

4. When you insert rows at row 7 are the same number of rows inserted at 11?


Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

[SuperOnlineGuy]

You only one Macro on the Sample but two buttons. Why?

This is because I was changing the template to more accurately look like what I had in mind. The second button currently does nothing, but I plan on adding a macro to it.

1. Get rid of the buttons. Clicking in Column A can run all the macros that you might Need.

I need two buttons, as the top section and bottom section may have different amount of rows. This was a request made by user. This is why two buttons are present.

2. I would not hide Rows 7 and 11. there is no need. Let your user enter the data in those rows.

Rows will be hidden in case they are not used. It gives a cleaner final product. This was another request from users.

4. When you insert rows at row 7 are the same number of rows inserted at 11?

Yes. The code will copy the hidden row at 11 first, then paste the specified number of rows. Then copy row 7, and paste the number of rows.

[mehmetcik]

OK I understand.

We can get rid of the buttons. Select "ID Column" or "ID" to run the Macros

I have written the code for you

Select "ID Column" or "ID" to run the Macro.

Please Login or Register  to view this content.