Add row above button

[m27mitchell]

Hello,

I've done this before but it's been a very long time, and I cannot remember how to do it to save my life. How can I insert a button in a row, at the end of a list, that users can click just to add a new editable row above? I've not done anything with VBA or macros for a while, and honestly don't know much about either.

Thank you,

 

[Truiz]

Where will the button be located, what column row A5? C8?

 

[m27mitchell]

As of right now the last line is in row 195. I have it set up with 3 columns. A=Last Name, B=First Name, C=Title. I want to put the button where row 196 is so that anyone that needs to add an employee after I've finished with my workbook, they can click the button and it insert a line above, so that the formulas pulling from this sheet will always keep the new ones added into the equation. I know if I write a formula on another sheet in the workbook, and tell it to pull info from =Employees!A1:C195 that it won't pull anything added into row 196 automatically.

 

[Truiz]

m27mitchell The best solution (I believe) would be to format your data as a table, that way whenever you enter a new row the formulas will automatically fill down, so asumiong you already have the table set up as you want it to look, add a new macro and paste the following code

Sub Addbtmrw()


' Change "TableName" with the name of your table (Keep the "")
'
'
ActiveSheet.ListObjects("TableName").ListRows.Add AlwaysInsert:=True
    
End Sub

Add the button just below the table that way every time a new row is added the button will move as well.

Let me know if this works for you

 

[m27mitchell]

Thank you for responding. They've changed it up on me a bit now. Instead of the employee names being in order from top row, down, they are now set up like my example below. Across in columns. It's employee name in the top row, then 5 competencies under each name. In another sheet of my workbook, I have formulas pulling info that corresponds with whatever name is picked from a drop down list of the employee names. Is there a way to do the button to add a column, instead of a row as I was trying to do previously? It would be the same type of thing as far as I need it to update the formula if any column is added, etc. I so appreciate all your help with this. After trying to post this, I see that it's throwing it all out of wack with my example below. What I was trying to get across was that for example: Jones, Joe (A1), then his first competency is (A2) Attention to Detail, Barber, Ben (B1), then his first competency is (B2) Communicator, etc...

A B C D

1 Jones, joe barber, ben mitchell, may street, jim
2 Attention to detail Communicator Easily Adaptable Customer Focus
3 Problem solving Technical Decision Making & Problem Solving Integrity
4 team work Reliable Flexibility Planning and Organizing

 

[Truiz]

Same thing format the info as a table then try this code

Sub AddColumn()

' Remember to change the table name to the name of your table
' Place the button next to the last column so that it would move with the table
    
    ActiveSheet.ListObjects("Table1").ListColumns.Add


End Sub

 

[m27mitchell]

How do I insert the button? Is it one from the Insert under either the 'Form Controls' or 'Active X Controls'? Once I click one, then where do I paste the code?

 

[Truiz]

Ok so the first thing you need to do is adding the code creating the macro if you will,

• Open the excel file in which you want to use the Macro.
• Press Alt + F11 (or equivalent in Mac) This will open the Microsoft Visual Basic for Applications (<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym>)
• Click "Insert" on the menu bar then "Module" It will open an editor
• Paste the provided Code
• Go back to the excel sheet and add the button using the "Form Controls"
• insert the button to the right side of the table this way every time you click it and add a column the button will move.
• Select the Macro you want to apply to the button.
• Enjoy.