Help with Private Sub macro involving protected sheet and Data Validation

U

uhguy

Board Regular
Joined
Aug 22, 2008
Messages
79
Hi

The macro below works only if the sheet is not protected. How can we make it work when the sheet is locked? For all my module Macros i use a "unlock sheet" command but I have no idea how to do that with this macro since its a private sub and im not sure how or when to trigger the "call unlocksheet" macro I made.


Thanks!

Note on what this macro does: I have two sheets. Sheet one has a header on row 6 and the user is required to select from a drop down list in column C. The list is contained in sheet2 which i named the range as "DataList". I want to prevent a user from copying in data into column C on sheet one since it would get rid of the data validation i have in place. to prevent this, the macro below would undo last command if the range that should contain validations no longer has a validation in any cell. Also note that the range in sheet one is named "ValidationRange" and before this macro is written, every cell in that named range should already have a validation in place. If you have a better method than this id love to hear it. Thanks again!

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)



   If HasValidation(Range("validationrange")) Then
    
    

Exit Sub
      
Else

        Application.Undo
      MsgBox "Your last operation was canceled. " & _
      "It would have deleted data validation rules.", vbCritical
  '    Call ProtectIN
   End If
End Sub

Private Function HasValidation(r) As Boolean



   On Error Resume Next
  x = r.Validation.Type
 If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
The fact that it's a private sub doesn't mean anything in this case, it just means that only procedures within that sheet module can see that sub. Use the same .Unprotect as you use elsewhere, right before you need to start writing to the sheet.

Code: 
worksheets("yoursheetname").Unprotect "pw"
Application.Undo
worksheets("yoursheetname").Protect "pw"
 
Upvote 0
ChrisM said:
The fact that it's a private sub doesn't mean anything in this case, it just means that only procedures within that sheet module can see that sub. Use the same .Unprotect as you use elsewhere, right before you need to start writing to the sheet.

Code: 
worksheets("yoursheetname").Unprotect "pw"
Application.Undo
worksheets("yoursheetname").Protect "pw"
Click to expand...

Hi Chris,

I tried this but it doesn't work. the macro doesn't call for sheet to unlock when i try to copy paste. I think it doesnt know it has been triggered (or the command copy OR paste don't cause a trigger). Any thoughts? see the code below.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPassword As String
myPassword = "pw"


If HasValidation(Range("validationrange_Country")) Then
    
 
    
    
    

Exit Sub
      
Else

Worksheets("Sheet1").Unprotect Password:=myPassword
Application.Undo
Worksheets("Sheet1").Protect Password:=myPassword

       
      MsgBox "Your last operation was canceled. " & _
      "It would have deleted data validation rules.", vbCritical
  
   End If
End Sub

Private Function HasValidation(r) As Boolean

On Error Resume Next

  x = r.Validation.Type

 If Err.Number = 0 Then HasValidation = True Else HasValidation = False

End Function
 
Upvote 0
Im thinking it should be something like this obviously with working code lol



Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPassword As String
myPassword = "pw"


If HasValidation(Range("validationrange_Country")) Then
    
 
    
    
    

Exit Sub
      
Else
[B][COLOR=#ff0000]
Trigger next command if copy/cut command is activated[/COLOR][/B]

Worksheets("Sheet1").Unprotect Password:=myPassword
Application.Undo
[B][COLOR=#ff0000]
'this code below actually works as is now. If i have the sheet unlock to start with, the macro locks it after the undo action. The problem is above[/COLOR][/B]
Worksheets("Sheet1").Protect Password:=myPassword

       
      MsgBox "Your last operation was canceled. " & _
      "It would have deleted data validation rules.", vbCritical
  
   End If
End Sub

Private Function HasValidation(r) As Boolean

On Error Resume Next

  x = r.Validation.Type

 If Err.Number = 0 Then HasValidation = True Else HasValidation = False

End Function
 
Upvote 0
Oh, I may have misunderstood your issue. The Change procedure WILL fire when you copy a value to a cell, so if yours is not then you need to figure out why. It must be your test for the validationrange_country. Is that a worksheet or workbook-level named range? Try running the code without that test, just make sure you paste only in to the correct cell. You can comment out all the IF-Then lines (plus the exit sub line).
 
Upvote 0
This is happening all withing the same worksheet.
Sorry but im new at this and dont understand your last sentence :confused:. I dont know what happens if I run it with out the test, but the whole purpose of the macro is to check if the range "validationrange_country" still has a valid validation rule after user pastes data there, if even once cell does not, the macro undoes the last command, which would have been the "Paste" command.
 
Upvote 0
can this code be modified to be inserted before the undo line? the way i read it, it says to run the macro named "Unprotect" when the copy command is used. I tried inserting that in the macro but didnt do anything. maybe this doesnt say what i hoped it said...

'Application.CommandBars("Edit").Controls("Copy").OnAction = "Unprotect"
 
Upvote 0
Still not sure we're on the same page here, but to start back at the beginning, if you don't want the user messing with the values in column C then why not just use the sheet protection to lock the user out of those cells?

back to your original code, this sample code works for me, but I think there are some issues in your case I don't fully understand:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Sheet1.Unprotect "pw"
    Application.Undo
Sheet1.Protect "pw"
Application.EnableEvents = True
End Sub
 
Upvote 0
Holy smokes! i got it!! see below

Chris- sheet1 is a blank table when the user gets it. they are supposed to fill it out and one of the columns (column C) has a Validation drop down menu on all cells from C6:C10000. I named this range "validationrange_country". the cells in this range are not locked, even when the sheet it self is protected. I did this by right-clicking and selecting FORMAT CELLS then PROTECTION and un-checking the box that says LOCK. I did this because the users need to fill out column C manually. all the other columns have formulas that i dont want the user to mess with. That's why i need the sheet locked.

Since column C is not locked even when the sheet is protected, users are normally able to copy and paste anything in there. The problem is that if they use the regular paste, they will cause the drop down menu to disappear.

So with the macro below prevents a regular paste in the range i named "validationrange_country" if it removed a validation.

The code below should work now.

The new issue is that although they can not use a regular paste, they can still paste special value even if the data pasted does not conform to the validation rule.

My validation rule requires user to select from a list in the drop down. Help with this would be appreciated!
Hope this helps someone.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPassword As String
myPassword = "pw"

If HasValidation(Range("validationrange_Country")) Then
    
    
                        
                        Range("A1").Select
                                   Worksheets("Sheet1").Unprotect Password:=myPassword
                                   
                                   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

On Error Resume Next

  x = r.Validation.Type

 If Err.Number <> 0 Then HasValidation = True Else HasValidation = False

End Function
 
Upvote 0
NEVER MIND. IT still doesnt work. i had the sheet unlocked to begin with! ugh.. back to the drawing board
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Data List Change + PDF Export on button click
Replies
4
Views
80
StevenE06
S
W
  • Question
Protected sheet disables macro buttons
Replies
5
Views
337
warda11
W
H
  • Solved
Private sub to generate Text
Replies
5
Views
185
mumps
mumps
N
  • Question
Macro to Clearing Contents of Row affect Validation Lists
Replies
0
Views
208
Nathan Asius
N
G
  • Question
Macro Code Help for multiple worksheets.
Replies
1
Views
176
Georgiboy
Georgiboy

Forum statistics

Threads
1,214,920
Messages
6,122,272
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top