insert row above button (retrieve position of button)

[fiddystorms]

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.

[fiddystorms]

Is this too challenging a problem to answer? Please let me know so I don't pursue an answer. If this is too difficult for you guys, then its way too much for me to figure out.

[racksjackson]

Rows("1:1").Select
Selection.Insert Shift:=xlDown
type this code on button click

[fiddystorms]

Thank you very much, I will.

[fiddystorms]

Rows("1:1").Select
Selection.Insert Shift:=xlDown
type this code on button click

That seems to insert a single row into row 1. My situation is more dynamic in that I need the rows to be added directly above my forms command button which could be anywhere on the worksheet (there will be 12 in total).

I would like to specify the number of rows to add through an input box and I would like to maintain the format and formula (but not the content) of the 2 lines directly above the button (2 lines because they are shaded with a different color for distinction) if possible.

[racksjackson]

Hi fiddystorms,
I am mentioning below your solution :


Private Sub CommandButton6_Click()

bankData As Range
Set bankData = Range("hello_button")
Selection.Sort Key1:=Range("g16"), Order1:=xlAscending
Range("g16").Select
End Sub


from this code you could determine cell address then you could add rows at above of your button.

[fiddystorms]

Thank you RacksJackson foor trying. Here's what I did...

I used an active X command button, right clicked it and chose "view code"
that took me to the VBE where I pasted your code & nothing else.
went back to excel 2010 and clicked the button and got an error
error reads: Compile Error: Statement Invalid Outside Type Block.
Bank Data as Range is highlighted in blue until I click OK and then Private Sub CommandButton1_Click() becomes highlighted im yellow

Do I need to set a range?

[fiddystorms]

OK, let me take a step back and see if I can make it easier for everyone. If an input box is whats causing hesitation in replying, we can simplify it by adding 14 rows at a time. that should equate to 2 days of visits roughly.

i recorded the following macro & it works well, it just doesn't add the rows above the button... it continually adds them in the same spot every time.

How can I get it to add the rows above the button (dynamic) instead of always in the same spot (static) which appears to be row 405.

Sub Add_14()
'
' Add_14 Macro
'

'
Rows("405:418").Select
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 407
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 400
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 397
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 395
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 393
ActiveWindow.ScrollRow = 395
ActiveWindow.ScrollRow = 396
ActiveWindow.ScrollRow = 397
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 400
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 403
ActiveWindow.ScrollRow = 404
Selection.Copy
Selection.insert Shift:=xlDown
Rows("419:419").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 16
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = -0.249946592608417
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

[mikerickson]

If the button is from the Forms menu:[vba]ActiveSheet.Shapes(Application.Caller).TopLeftCell.EntireRow.Insert[/vba]