Using VBScript to Not Allow Column insert/delete

Posted on 2013-10-21
Medium Priority
Last Modified: 2013-11-13
I have VBScript that is running validation on 'save' command...the script will not work if I place the 'Protect Sheet' on with a password.  I need to disallow the user to add or delete columns or change the column heading name.  The reason being is that the information in this spreadsheet, then gets imported into an Access DB, so the specific column names and amount of columns must stay in tact.  How can I add this type of protection within the VBScript?
Question by:Lee R Liddick Jr
  • 2
LVL 54

Expert Comment

ID: 39588038

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:= _

Open in new window

LVL 14

Expert Comment

ID: 39590345
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, _
                .EnableSelection = xlNoRestrictions
                On Error Resume Next
                .Unprotect Password:=PW
            End If
        End If
        ProtectSheet = .ProtectContents
    End With
End Function

Open 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.

Author Comment

by:Lee R Liddick Jr
ID: 39594304
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.
LVL 14

Accepted Solution

Faustulus earned 2000 total points
ID: 39595978
The problem is the CurrentRegion property which seems to be inaccessible on a protected worksheet. I have replaced it. The new line is the last line in the following segment of your code.
Function EntryValidation() As Boolean
    Dim Ws As Worksheet
    Dim Cell As Range
    Dim R As Long, C As Long
    Set Ws = ThisWorkbook.Worksheets("Sheet1")      ' modify sheet name as required
    R = NwsFirstDataRow
    With Ws
        Do While Application.WorksheetFunction.CountA(.Rows(R))
            For C = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column

Open in new window

The workbook I attach is fully functioning as advertised.

I noticed that column H (Gender) now has a validation of "NoBlank-NoNumber". Didn't you have some fancy footwork there at one time that converted everything but "Uncle" to "M"?

Featured Post

Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

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.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question