VBA Doesn't Work With Protected Spreadsheet

[jaylam]

Let me begin by saying I am new to VBA. I have research for almost two days now and i have not yet found an answer to my question. I am making a template for over 500 users. This template is for asset management. My template contains data validation. I realized that if a user was to copy and paste something from another spreadsheet into the one with data validation, it destorys the data validation. I did some research and I found this script.

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function


While this VBA works, it only works when my sheet is unprotected. I want to prevent users from removing / adding columns in my template, so I have my sheet protected. But if I have my sheet protected, this vba doesn't run and users are able to copy / paste into columns that have data validation. Please Please Please Help!!!!

[xladept]

Hi Jaylam,

If it's a template, don't the users only get a copy???

[jaylam]

Hi xladept,

Thanks for replying. I am kind of confused as to what you mean "don't the users only get a copy" The template is stored in our shared drive. From there, users will use the template to fill out their asset information from their site and submit it for importing.

[Mordred]

Why not just unprotect the sheet while the code is running and then reprotect before the code finishes?

Please Login or Register  to view this content.

[jaylam]

Hi Mordred,

I tried doing that, but it doesnt work. Below is my vba. What I did first was protect the sheet with password "xyz" and then the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Unprotect Password:="xyz"
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
Sheet1.Protect Password:="xyz"
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

[Mordred]

Maybe if you control the cells that the users are able to work with. For instance, the following code allows users to work with the range from B2 to E2

Please Login or Register  to view this content.

Incorporate something like this into your workbook and see how it goes.

[jaylam]

Hi Mordred,

Thanks for all the help. As I mentioned earlier, I am really brand new to VBA. This is actually the first time I worked with it. With the code you mention, where do I input it? This is what I have and i keep getting debug messages. I put the range as "A1:XFD1" because I dont want users to work with columns only. Also, do i have to protect the sheet for this code to work? Thanks again for your help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim UsableRng As Range
Set ws = Worksheets("Sheet1")
Set UsableRng = ws.Range("A1:XFD1")
usabeRng.Locked -True
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If

End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

[rollis13]

Maybe changing your Worksheet_Change. The cells in the ValidationRange must not be blocked.

Please Login or Register  to view this content.

[Mordred]

Try using my example in a different module to set up the page, so something like

Please Login or Register  to view this content.

Once that is done you should be able to run your original code ie:

Please Login or Register  to view this content.

as long as the range that you provided is the same as the range that the user's will be able to access.

[jaylam]

Hey Mordred,
Do I still include the Worksheet1.Protect vba? I want the user to be able to format "all cells" besides "column" That is the only thing I dont want them to touch. I have attached the template. Please take a look at my spreadsheet and tell me what I did wrong. I have column "a" name ValidationRange for now.

[xladept]

Just a thought:

Please Login or Register  to view this content.