Protected Excel Worksheet Help
I have the following code in my spreadsheet to allow for multiple selections in all drop down lists. I need to protect the sheet and keep the functionality of this code. I know I can add a couple of lines to make it work; however, I an in no way a programmer. Can you help me?
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$10" Or Target.Address = "$C$16" Or Target.Address = "$C$17" Or Target.Address = "$C$19" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
I do still need assistance
I do still need assistance with this issue. I have to have the sheet protected and the code still work. I am frustrated because I requested a quote and haven't heard a word in a week or more. I would greatly appreciate any assistance. Thanks.
There may be another way to
There may be another way to do it but I find protecting a sheet without using any password is ideal for this sort of scenario.
At the beginning of your macro enter the following:
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect ""
End If
Then just before the end sub at the end enter:
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect ""
End If
This will automatically unprotect the sheet before running the macro (provided no password was entered when assigning sheet protection) and protect it at the end of the macro running