Macro to Unprotect and Protect

I have an excel spreadsheet that I want to distribute to everyone in my department. The file is linked to a list on SharePoint with several thousand names. Right now, I have an advanced filter set up (but hidden) so that anyone (regardless of excel ability) can type a name the search box and click the (macro enabled) search button to find a name in the large list. The macro that I recorded refreshes the data from SharePoint and reapplies the advanced filter every time the search button is clicked.

I want the sheet to be protected so that people cannot alter or delete anything in the file. Unfortunately, when I protect the sheet (with a password), the macro won't run. I tried to work the unprotecting and re-protecting of the worksheet into the macro, but the password dialog box pops up while the macro is running.

I have searched online for a solution, and it sounds like there is, but it involves VBA, which I know nothing about. My macro ability is limited to recording macros.

Any help would be appreciated.

Thanks!

I am not sure I understand your solution.


I did a bit more Macro recording after my initial post, so I will describe the current situation/dilemma.

The sheet is protected right now without a password. When the file is distributed to the department, the sheet will already be protected. The macro enabled button is also protected, so no one can alter the button, list, or hidden advanced filter. When they click the macro enabled button, it unprotects the sheet, updates from SharePoint, applies the advanced filter, and then re-protects the sheet.

This is exactly what I want it to do, but I want the sheet to be protected by a password. At the moment, anyone could unprotect the sheet with the click of the mouse and delete the macro button or alter the advanced filter.

I apologize in advance if I am breaking any rules or not following guidelines (I am very new to the forum). Below is what my recorded macro looks like in the VBA editor:

Please Login or Register  to view this content.

Is there a simply line of code that I could insert that would type the password in to unprotect the sheet and then again to re-protect the sheet?

Thanks!

This works perfectly! Thanks so much!

Hi,

I am having a similar problem but I am not clear if the code applies to my situation or where I would insert the code into my existing macro.

My situation is that I also want to protect my worksheet so that staff don't unwittingly alter codes and values; however, the sheet also has a macro that allows users to select more than one option for one cell. If I protect the worksheet, the macro won't work.

How can I use the macro AND protect the worksheet?

Thanks,
Lyndy

Originally Posted by HaHoBe

Hi, MBeedle,

you may pass the password when protecting and unprotecting the sheet. Please try and alter your password as needed:

Please Login or Register  to view this content.

I think the code may be worked on to simplify it a bit.

Ciao,
Holger

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:

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

Hi Holger,

My bad for not being aware of the forum rules. Please accept my apologies.

I have printed them out and read them. Point taken on 3 rule but if I have a follow up question relating to a solution, I don't see how that's a new question as I am asking for a clarification on a solution. But to be safe every time, I will post a new thread and put a link to a previous relevant question.

Lyndy