FelixExcel
New Member
- Joined
- Sep 14, 2022
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
Hey all,
I created two command buttons in excel that both insert a row at different points in the excel sheet and add formulas and format the cells in that newly inserted row. I was very happy with how it worked until I noticed that when I use the upper button to insert a row at the top of my excel page, the second command button doesnt do its job correctly anymore, as the number of rows changed, so the second command button inserts the new row at the wrong place. In the code below you can see that the command buttons are linked to exact cell locations (A21 for example). Therefore, when a new row is added from the first command button cell locations from the second command button are wrong. Is there a way I can solve this? I already had some success using Cells.find("Auftrag").EntireRow.Insert but using this I dont know how to format the newly added row and add formulas to specific cells in that row.
Can someone help?
Private Sub CommandButton1_Click()
Sheets("Tabelle1").Range("A21").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
Sheets("Tabelle1").Range("A21").Select
ActiveCell.Value = " Nachtrag"
Sheets("Tabelle1").Range("G21").Select
ActiveCell.Formula = "=(e21*0.19)"
Sheets("Tabelle1").Range("H21").Select
ActiveCell.Formula = "=(E21*1.19)"
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeLeft).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeRight).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeTop).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeBottom).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Interior.ColorIndex = 24
End Sub
Private Sub CommandButton2_Click()
Sheets("Tabelle1").Range("A41").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
Sheets("Tabelle1").Range("A41").Select
ActiveCell.Value = "Abschlagsrechnung"
Sheets("Tabelle1").Range("G41").Select
ActiveCell.Formula = "=(e41*0.19)"
Sheets("Tabelle1").Range("H41").Select
ActiveCell.Formula = "=(E41*1.19)"
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeLeft).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeRight).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeTop).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeBottom).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Interior.ColorIndex = 24
End Sub
I created two command buttons in excel that both insert a row at different points in the excel sheet and add formulas and format the cells in that newly inserted row. I was very happy with how it worked until I noticed that when I use the upper button to insert a row at the top of my excel page, the second command button doesnt do its job correctly anymore, as the number of rows changed, so the second command button inserts the new row at the wrong place. In the code below you can see that the command buttons are linked to exact cell locations (A21 for example). Therefore, when a new row is added from the first command button cell locations from the second command button are wrong. Is there a way I can solve this? I already had some success using Cells.find("Auftrag").EntireRow.Insert but using this I dont know how to format the newly added row and add formulas to specific cells in that row.
Can someone help?
Private Sub CommandButton1_Click()
Sheets("Tabelle1").Range("A21").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
Sheets("Tabelle1").Range("A21").Select
ActiveCell.Value = " Nachtrag"
Sheets("Tabelle1").Range("G21").Select
ActiveCell.Formula = "=(e21*0.19)"
Sheets("Tabelle1").Range("H21").Select
ActiveCell.Formula = "=(E21*1.19)"
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeLeft).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeRight).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeTop).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Borders(xlEdgeBottom).Weight = xlThin
Sheets("Tabelle1").Range("E21").Select
ActiveCell.Interior.ColorIndex = 24
End Sub
Private Sub CommandButton2_Click()
Sheets("Tabelle1").Range("A41").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
Sheets("Tabelle1").Range("A41").Select
ActiveCell.Value = "Abschlagsrechnung"
Sheets("Tabelle1").Range("G41").Select
ActiveCell.Formula = "=(e41*0.19)"
Sheets("Tabelle1").Range("H41").Select
ActiveCell.Formula = "=(E41*1.19)"
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeLeft).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeRight).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeTop).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Borders(xlEdgeBottom).Weight = xlThin
Sheets("Tabelle1").Range("E41").Select
ActiveCell.Interior.ColorIndex = 24
End Sub