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
AXISHKAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Worksheets("Input").Protect AllowInsertingColumns:=True, AllowInsertingRows:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True
0
 
Saqib Husain, SyedEngineerCommented:
You can use worksheet protection for this. Why do you want to do this with VBA?
0
 
Tej Pratap Shukla ~DexterConnect With a Mentor Server AdministratorCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Saqib Husain, SyedEngineerCommented:
The VBA for the same is

    ActiveSheet.Protect DrawingObjects:=AllowInsertingColumns:=True, AllowInsertingRows:=True, _
        AllowDeletingColumns:=True, AllowDeletingRows:=True
0
 
AXISHKAuthor Commented:
How to modify the coding for checking the "INPUT" WORKSHEET only ?

Tks
0
 
AXISHKAuthor Commented:
Tks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.