Way to Get Around Macros Not Working in Protected Sheet?

**lyndy256** · 05-20-2013, 10:43 PM

Hi,

Is there a way to get around macros not working in a protected sheet? I want to allow user to enter multiple responses using the following code, but it won't work if sheet is protected.

Please Login or Register  to view this content.

Is there a code I can use to override the disabling of macro when I protect sheet? I want to protect sheet so users don't accidentally change codes, field titles, lists, etc....

Thanks,
Lyndy

Moderators note: code tags added for you - this time

**HaHoBe** · 05-21-2013, 11:38 PM

Hi, lyndy256,

you may pass the parameter UserInterfaceOnly:=True when protecting your sheet which would enable macros to run but keep users dfrom editing (please mind this is a volatile settiung and won´t get stored with the workbook so the action should be called either in the Workbook_Open-event or when activating the sheet):

Please Login or Register  to view this content.

Using this will keep the sheet protection even if due to run time error the code may fail while with unprotecting the sheet at the start it will be fully accessible unless an error handler leads the code to protect the sheet on error or at the end of the procedure.

HTH,
Holger

**lyndy256** · 05-22-2013, 01:58 AM

Hi Holger,

Thanks for your reply.

Sorry if my follow up question is dumb: where exactly do I insert the code you provided-above or below my current code or in a separate macro? My code is below:

Please Login or Register  to view this content.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 11 Or Target.Column = 23 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub

Please Login or Register  to view this content.

Lyndy

**lyndy256** · 05-22-2013, 02:01 AM

BTW, I am hitting # sign as Forum Rules indicate but it's not showing code as example does.

The instructions say, " Select your code and click the # button at the top of the post window...."

I copy my code from my Excel Worksheet and insert into my message body. Then hit #. I'm not sure what I am doing wrong, but maybe someone can clarify what is meant by "select code."

Thanks,
Lyndy

**HaHoBe** · 05-22-2013, 11:44 AM

Hi, lyndy256,

the code you posted needs the event to be triggered so any action regarding protecting the worksheet needs to be done prior to that. That´s why I mentioned the Workbook_Open event in ThisWorkook where the protection with UserInterfaceOnly:=True may take place and thus allow your macro to run.

Code in ThisWorkbbok:

Please Login or Register  to view this content.

Code behind "Tabelle1"

Please Login or Register  to view this content.

Ciao,
Holger

**lyndy256** · 05-22-2013, 10:12 PM

Got it!

Thanks for your patience. I am new to coding and I do better with more explicit instructions, though I appreciate if I want to learn coding I need to learn the language of coding.

I see from looking at the attachment you sent through that the answer to my question is that the macro that allows the code to work even if worksheet is protected must be kept separately and before the main macro that allows multiple entries in one cell. This is how I understand it. "The Workbook_Open event in ThisWorkook where the protection with UserInterfaceOnly:=True may take place and thus allow your macro to run" doesn't make much sense to me.

Cheers,
Lyndy