|
|
|
|
|
|
Excel
|
Prevent Row / Column Removal
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002
|
Submitted by:
|
Justinlabenne
|
Description:
|
Prevent a user from deleting rows or columns from any worksheet
|
Discussion:
|
This is an "Event Hack" of sorts, as Excel does not provide direct functionality that enables you to "catch" a row or column has been deletion.
This code is stored in the Thisworkbook code module and will prevent any rows or columns from being deleted on any sheet. It can be switched to a single worksheet event if it is only needed for a single sheet.
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ((Target.Address = Target.EntireRow.Address Or _
Target.Address = Target.EntireColumn.Address)) Then
With Application
.EnableEvents = False
.Undo
MsgBox "No deleting rows or columns", 16
.EnableEvents = True
End With
Else
Exit Sub
End If
End Sub
|
How to use:
|
- Open an Excel Workbook
- Copy the code
- Right Click on the small Excel Icon in the left hand corner > View Code
- Paste code into the right pane
- Press Alt+Q to return to Excel
- Save workbook before any other changes
|
Test the code:
|
- Try deleting a row or column from any worksheet.
- As long as macros are enabled, the code will prevent this from happening.
|
Sample File:
|
Prevent Row-Column Removal.zip 7.23KB
|
Approved by mdmackillop
|
This entry has been viewed 194 times.
|
|