Using VBScript to Not Allow Column insert/delete

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?
Lee R Liddick JrReporting AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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

Open in new window

Regards
0
FaustulusCommented:
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

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.
0
Lee R Liddick JrReporting AnalystAuthor Commented:
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.
0
FaustulusCommented:
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"?
EXX-131024-Data-Validation.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.