Solved: Insert/Delete Row Between Entry

[gnod]

Hi,

this is the currently design of my fund transfer template.. the worksheet is protected - the password is "password"..

i need to insert/delete a row between entry (for ex: insert feb 11 between feb 10 and 12) or whichever the user wants to insert/delete a row..


Thanks..

[ammx]

Hi,

try these ones:

[VBA]Option Explicit
Public Const strPassword As String = "password"
Sub InsertRow()
Dim intFinalRow As Integer

Application.ScreenUpdating = False

intFinalRow = Cells(65536, 1).End(xlUp).Row

ActiveSheet.Unprotect strPassword
With Range("A" & intFinalRow)
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy
.Offset(-1, 0).PasteSpecial xlPasteAll
End With
ActiveSheet.Protect strPassword

Application.ScreenUpdating = True
End Sub
Sub InsertBetweenRow()
Dim intActiveRow As Integer

Application.ScreenUpdating = False

intActiveRow = ActiveCell.Row

ActiveSheet.Unprotect strPassword
With Range("A" & intActiveRow)
.EntireRow.Insert
.Offset(-2, 0).EntireRow.Copy
.Offset(-1, 0).PasteSpecial xlPasteAll
End With
ActiveSheet.Protect strPassword

Application.ScreenUpdating = True
End Sub
Sub DeleteRow()
Dim intActiveRow As Integer

Application.ScreenUpdating = False

intActiveRow = ActiveCell.Row

ActiveSheet.Unprotect strPassword
With Range("A" & intActiveRow)
.EntireRow.Delete
End With
ActiveSheet.Protect strPassword

Application.ScreenUpdating = True
End Sub
[/VBA]


Greets,


Michael

[Bob Phillips]

[vba]

Sub InsertRow()

Application.ScreenUpdating = False

ActiveSheet.Unprotect strPassword
With Rows(ActiveCell.Row)
.Insert
.Offset(-2, 0).EntireRow.Copy
.Offset(-1, 0).PasteSpecial xlPasteAll
End With
ActiveSheet.Protect strPassword

Application.ScreenUpdating = True
End Sub
[/vba]

[gnod]

Thanks ammx & xld

[gnod]

is it possible to right click the cell to make an insert/delete rows in a protected sheet instead of a button?
(for ex:, when the user right click, the right click menu will have "Insert Between" or "Delete Selected")

Thanks

[Bob Phillips]

Use the modified code I gave you before and add this

[vba]
Option Explicit

Private Const MENU_NAME As String = "Insert New Row"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application.CommandBars("Cell")
On Error Resume Next
.Controls(MENU_NAME).Delete
On Error GoTo 0
End With
End Sub

Private Sub Workbook_Open()
With Application.CommandBars("Cell")
On Error Resume Next
.Controls(MENU_NAME).Delete
On Error GoTo 0

With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = MENU_NAME
.OnAction = "InsertRow"
End With
End With
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[gnod]

Thanks xld