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:

  1. Open an Excel Workbook
  2. Copy the code
  3. Right Click on the small Excel Icon in the left hand corner > View Code
  4. Paste code into the right pane
  5. Press Alt+Q to return to Excel
  6. Save workbook before any other changes
 

Test the code:

  1. Try deleting a row or column from any worksheet.
  2. 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.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express