• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1082
  • Last Modified:

Prevent insert or delete rows and column with Excel VBA

Is it possible to prevent a user from insert / delete a row or column on a particular worksheet with Excel VBA ?

Tks
0
AXISHK
Asked:
AXISHK
  • 3
  • 2
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
You can use worksheet protection for this. Why do you want to do this with VBA?
0
 
Tej Pratap Shukla ~DexterCommented:
Hi Axishk..

you should be able to accomplish this by protecting your sheet with the right parameters,

Insert the code in your Object Module to disable inserting Cells.


Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

     'ProtectSheet
    ActiveSheet.Protect AllowInsertingColumns:=False, AllowInsertingRows:=False, UserInterfaceOnly:=True         
End Sub 

Open in new window


Code to prevent Deletion of rows and coloums

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

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
The VBA for the same is

    ActiveSheet.Protect DrawingObjects:=AllowInsertingColumns:=True, AllowInsertingRows:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
AXISHKAuthor Commented:
How to modify the coding for checking the "INPUT" WORKSHEET only ?

Tks
0
 
Saqib Husain, SyedEngineerCommented:
Worksheets("Input").Protect AllowInsertingColumns:=True, AllowInsertingRows:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True
0
 
AXISHKAuthor Commented:
Tks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now