I have an Excel spreadsheet that has some locked cells. The Protection I have set is that locked cells cannot be selected by the user.
I would like to create a VBA solution that allows the user to select one of the locked cells, change the font properties of that cell and then reset the Protection for the sheet.
I tried with code that unprotects the sheet, shows a MessageBox that prompts the user to select the cell he wants to change, changes the properties and then reprotects the sheet. It's as follows:
Dim Response As String
ActiveSheet.UnProtect
Response = MsgBox("Select the cell you want to hide", 1, "Hide a Candidate - Step 1 of 2")
If Response = vbCancel Then _
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
Application.Wait Now + TimeValue("00:00:05")
Response = MsgBox("Click OK", 1, "Hide a Candidate - Step 2 of 2")
If Response = vbCancel Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
ActiveCell.Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
But since the MsgBox is modal, I can't select a cell until a button on the MsgBox is clicked. The Application.Wait ... didn't solve that problem.
Some assistance with the coding that does what I'm aiming to do would be appreciated. I'm sure that the coding could also be tightened up. I'm still learning ....
TIA
David
