PDA

View Full Version : Solved: Insert/Delete Row Between Entry



gnod
02-12-2007, 08:38 AM
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
02-12-2007, 09:14 AM
Hi,

try these ones:

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



Greets,


Michael

Bob Phillips
02-12-2007, 09:50 AM
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

gnod
02-12-2007, 10:26 AM
Thanks ammx :clap: & xld :clap:

:thumb

gnod
02-12-2007, 10:54 AM
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
02-12-2007, 11:03 AM
Use the modified code I gave you before and add this


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


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
02-13-2007, 09:32 AM
:bow:

Thanks xld :clap: :clap: :clap:

:thumb