Using VBScript to Not Allow Column insert/delete

Hi,

pls try to use protect

    Cells.Locked = False
    Rows("1:1").Locked = True
    ActiveSheet.Protect AllowInsertingColumns:=False, AllowDeletingColumns:=False, _
        DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingRows:=True, _
        AllowInsertingHyperlinks:=True, AllowDeletingRows:=True, AllowSorting:= _
        True

Select allOpen in new window

Regards

Insert a code module into your project, rename it as 'Admin' (or some other suitably indicative name) and paste the following code into it.

Option Explicit

' ========================================================
'   Change of password

'   The password can be changed by modifying
'       the value of Constant 'PassCode' below
'   BE SURE to run the procedure 'Unprotect_Sheets'
'       BEFORE changing the password
' ========================================================

    Const PassCode As String = "1234"
    
Sub Protect_Sheets()
    ProtectWb True, PassCode
End Sub

Sub Unprotect_Sheets()
    ProtectWb False, PassCode
End Sub

Private Sub ProtectWb(ByVal ProtectNow As Boolean, _
              PW As String)
    
    Dim Ws As Worksheet
    
    For Each Ws In ThisWorkbook.Worksheets
        ProtectSheet ProtectNow, PW, Ws
    Next Ws
End Sub

Private Function ProtectSheet(ByVal ProtectNow As Boolean, _
                              ByVal PW As String, _
                              Ws As Worksheet) As Boolean
    With Ws
        If ProtectNow <> .ProtectContents Then
            If ProtectNow Then
                .Protect Contents:=True, _
                         AllowInsertingRows:=True, _
                         UserInterfaceOnly:=True
                .EnableSelection = xlNoRestrictions
            Else
                On Error Resume Next
                .Unprotect Password:=PW
            End If
        End If
        ProtectSheet = .ProtectContents
    End With
End Function

Select allOpen in new window

There are four procedures in this code.
Protect_Sheets to establish proection and UnProtect_Sheets to remove protection. Both of these procedures are designed to be called from the keyboard, for example with a keyboard shortcut, or from the Macro button on the Developer tab. Both of them call the third procedure, ProtectWb. This one loops through all the sheets in your workbook and applies protection to all of them. If you wanted to exclude some sheets from this action it is easy to insert a filter here. The fourth procedure, ProtectSheet, is doing all the work, one sheet at a time. It will apply protection and the password. If you need to be more specific about the nature of the protection that can be adjusted here. Look for "AllowInsertingRows:=True". You can remove this line, if you don't want it, or add more properties like it in more lines like it, one under the other. The key point is that it applies protection for UserInterfaceOnly, meaning that any code in your workbook will be able to do what it wants while the user has no rights except to select any cell he wants and insert rows.
The code doesn't interfere in the locking or unlocking of cells. Cells that aren't locked will not be affected by whatever protection is applied. If you wanted the code to control the Locked property this could be added, differently for each sheet in your workbook meaning, you would have to say which sheets there are and how they are locked.
The password is determined by what you enter at the top of the code. If you want no password, set the variable PassCode = "". Please observe the instructions for changing the password.

The way to work with this code is to unprotect the sheet (run Sub Unprotect_Sheets), set your Locked properties, modify your sheet as desired. When finished, set the password you wish to apply, if any, in the variable PassCode, and run Sub Protect_Sheets.
Now, if you wish for no one to be able to access the code and run these same procedures to undo your work (or learn your password), protect the VBA project.
In the VBE window select Tools / VBA Project Properties ...  Select the 'Protection' tab. Lock the project for viewing and enter a password only you have the power to forget.  :-)

As you see, this system is very flexible. It could be automated, for example, to run automatically whenever the workbook is opened. That is useful if you frequently lift the protection and don't want to bother resetting it as described above. For a start, the simple version introduced above should do just fine.

Okay...this VB worked; however, I continue to get the error that says my original validation VB code does not work with a locked spreadsheet so it saves the file without checking the columns.