View Full Version : Solved: Insert/Delete Row Between Entry
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..
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
Thanks ammx :clap: & xld :clap:
:thumb
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
:bow:
Thanks xld :clap: :clap: :clap:
:thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.