Hello everyone! Because my VBA skills are almost non-existent, I don't know how to go about doing what I need to do.
So basically, I have a workbook that will be used by multiple users. There is a sheet with an excel table in it. Some columns have formulas and some need the user to type in data. As I need to protect the formulas in the sheet, I've locked the cells with formulas and kept the rest unlocked so I can protect the sheet.
The trouble is that in one of the columns (column Q) I have a formula which will return either a number or, if there is an error in the formula, it will return the text "Please input value". So I need this column to be locked except for cells where there is the text "Please input value". I need this formula to keep recalculating after any change in this sheet so I cannot lock the cell since the result might change.
I came up with a code to protect the sheet with a password by adding a button on the sheet (so the user cannot change the password when protecting the sheet). The button can protect and unprotect the sheet. This is my code (I also wanted the caption on the button to change depending on whether the sheet is protected or not):
Private Sub ToggleButton1_Click()
ToggleButton1.Caption = "Unprotect"
If ActiveSheet.ProtectContents = True Then
On Error GoTo Message
ActiveSheet.Unprotect
ToggleButton1.Caption = "Protect"
Else
ToggleButton1.Caption = "Unprotect"
ActiveSheet.Protect "Password123", AllowFiltering:=True, AllowSorting:=True
End If
Exit Sub
Message:
MsgBox ("Incorrect password entered. Please try again")
End Sub
I'm unsure if I can integrate additional code for locking cells in the above since the above code only runs when clicking on the Protect/Unprotect button and I actually need a macro to run all the time, I think, to pick up on any changes that might alter the result in cells in column Q. I also don't know if this will make the workbook significantly slower or not. But any help is appreciated. Thank you 
PS:I posted this on MrExcel as well but no luck there with finding a solution.
Bookmarks