fiddystorms
05-30-2012, 12:04 PM
Let me preface with the old speech about being a neophyte etc.
Searching the internet for some code to automate adding rows I found this, this is exactly what I needed with a couple exceptions:
Hi,
I want to create a simple macro that inserts a row above the button one
clicks. Since the position of this button will change when adding/removing
rows, I can not give a reference where to insert the row, therefore I want it
to be inserted relative to the position of the commandbutton. Can I get a
reference (rownumber) from such a button, such that I can base the
insertion point on this info?
also, if I have multiple buttons, can I reduce the code for each button, such that it retrieves its own name and calls the insert function with its own name?
Thanks a lot!
it was determined that it was a forms command
the answer was:
You can assign the same macro to each button from the Forms toolbar:
Sub FromFormsCommandBar2()
Dim Btn As Button
with ActiveSheet
Set Btn = .Buttons(application.caller)
btn.topleftcell.entirerow.insert
end with
End Sub
it worked pretty well with the exception of the formatting.
My question is:
Can this above code be amended to:
1. maintain the formulas and formatting of the 2 rows directly above the forms command button (because both lines are filled with a different color) but not the contents?
2. add an InputBox to specify the # of rows to add?
It is important to note that there will be 12 buttons in total (corresponding with the months of the year). The spreadsheet is used to track expenses and customer visits which will vary greatly month to month, so sometimes the month has to be expanded to account for this.
Searching the internet for some code to automate adding rows I found this, this is exactly what I needed with a couple exceptions:
Hi,
I want to create a simple macro that inserts a row above the button one
clicks. Since the position of this button will change when adding/removing
rows, I can not give a reference where to insert the row, therefore I want it
to be inserted relative to the position of the commandbutton. Can I get a
reference (rownumber) from such a button, such that I can base the
insertion point on this info?
also, if I have multiple buttons, can I reduce the code for each button, such that it retrieves its own name and calls the insert function with its own name?
Thanks a lot!
it was determined that it was a forms command
the answer was:
You can assign the same macro to each button from the Forms toolbar:
Sub FromFormsCommandBar2()
Dim Btn As Button
with ActiveSheet
Set Btn = .Buttons(application.caller)
btn.topleftcell.entirerow.insert
end with
End Sub
it worked pretty well with the exception of the formatting.
My question is:
Can this above code be amended to:
1. maintain the formulas and formatting of the 2 rows directly above the forms command button (because both lines are filled with a different color) but not the contents?
2. add an InputBox to specify the # of rows to add?
It is important to note that there will be 12 buttons in total (corresponding with the months of the year). The spreadsheet is used to track expenses and customer visits which will vary greatly month to month, so sometimes the month has to be expanded to account for this.