Validation code with Protected sheet

[sandu1]

Hello Guys,

I am using this code to filter out one of the drop down list based on cell entry and I have to protect the sheet post which this code doesnt seem to work, please help.
I tried unprotect and protect password in the code but that doesn't seems to work. Rest all other sheets with other codes are working fine.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim str1 As String
  If Target.Address(0, 0) = "K10" Then
     Range("B5").ClearContents
     On Error Resume Next
        Range("B5").Validation.Delete
     On Error GoTo 0
     str1 = LCase(Target.Value)
     If InStr(1, str1, "@gmail.com") Then
        Range("B5").Validation.Add Type:=xlValidateList, Formula1:="=vld_gmail"
     ElseIf InStr(1, str1, "hotmail.com") Then
        Range("B5").Validation.Add Type:=xlValidateList, Formula1:="=vld_hotmail"
     Else
        Range("B5").Validation.Add Type:=xlValidateList, Formula1:="=vld_all"
     End If
  End If
End Sub

Please help

[karedog]

Assuming that cell B5 and K10 is not locked, the code should be :

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim str1 As String
  If Target.Address(0, 0) = "K10" Then
     Range("B5").ClearContents
     On Error Resume Next
        Range("B5").Validation.Delete
     On Error GoTo 0
     str1 = LCase(Target.Value)
     Me.Unprotect Password:="abc"
     If InStr(1, str1, "@gmail.com") Then
        Range("B5").Validation.Add Type:=xlValidateList, Formula1:="=vld_gmail"
     ElseIf InStr(1, str1, "hotmail.com") Then
        Range("B5").Validation.Add Type:=xlValidateList, Formula1:="=vld_hotmail"
     Else
        Range("B5").Validation.Add Type:=xlValidateList, Formula1:="=vld_all"
     End If
     Me.Protect Password:="abc", UserinterfaceOnly:=True
  End If
End Sub

If above code is not worked, probably caused by other code that you have (as you stated here, you have 5 modules of code) :

Hello,

I am difficulties in running macro on protected sheets.

I have 5 modules also codes on each sheet. Now, I have to protect the sheet for unauthorized user input.

Its throwing an error while I try to execute the code now. Please suggest.

Thanks
Sandu

and we can't help without seeing all your code that may cause this, you need to post your workbook so we can analyze the problem.

[sandu1]

Thank you very much. I have an other question.

I have question with regards to below code. I have to use both the codes in the "workbook". Can I use both under same SUB?

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="abc", UserinterfaceOnly:=True
Next
End Sub

and other code is to block user adding new sheets

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
         ActiveSheet.Delete
        MsgBox "Sorry, Adding new sheets is not Allowed!", 16
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

Please suggest.

[karedog]

You don't need the second sub (Workbook_NewSheet), remove this sub.
Just protect your workbook with a password, and user cannot add any sheet to it.

http://www.excel-easy.com/examples/p...-workbook.html

[sandu1]

I had tried that earlier, if I protect my workbook it will be disable sheets. But, it will lead to another issue, as I have other hidden worksheets and macros running on the that worksheet will throw an error "unable to set the visible property of the worksheet class"

Sheets("Docs").Visible = True
ActiveWorkbook.Sheets("Docs").Copy _
After:=ActiveWorkbook.Sheets("Docs")

Above code will be at module 2 and has been assigned to button that will copy and data from the hidden sheet and creates a new sheet. Please advise on how to overcome this.

[sandu1]

Please suggest, if I have any other better option where I can disable user adding new sheets and can run the above mentioned code.

[karedog]

The method will be same with the sheet : unprotect --> make changes --> (re) protect.

For example the code for the sheet above :

....
....
     Me.Unprotect Password:="abc"
     Make any changes
     Me.Protect Password:="abc", UserinterfaceOnly:=True
....
....

For workbook, the code will be something like this (assuming the workbook is protected with password abc) :

....
....
ThisWorkbook.Unprotect Password:="abc"

'Code that make changes
ActiveWorkbook.Sheets("Docs").Copy _
After:=ActiveWorkbook.Sheets("Docs")

ThisWorkbook.Protect Password:="abc", Structure:=True
....
....

[sandu1]

Thank you very much, I will mark thread as resolved.

[karedog]

You are welcome, and thanks for marking the thread.


Regards