VBA lock cells based on their content

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.

Hi,

I don't understand. If the column Q cell is returning the message about inputting a value because the cell is currently returning an error, then presumably you want the precedent cells to the Q cell to be changed so that the formula does return a non error value. I'm not clear why protecting/unprotecting is involved

Hi Sinon05

Place this Code in the Sheet Module for Sheet Data...it has some hitches in it's giddyup...see if it works for you.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim LR           As Long
   Dim Comm         As Double
   LR = Range("Q" & Rows.Count).End(xlUp).Row
   If Not Intersect(Target, Range("Q2:Q" & LR)) Is Nothing Then
      Select Case Target.Value
      Case "Please input value"
         On Error Resume Next
         Application.DisplayAlerts = False
         Comm = Application.InputBox _
                (Prompt:="Please enter Commission.", _
                 Title:="ENTER COMMISSION", Type:=1)
         On Error GoTo 0
         Application.DisplayAlerts = True
         If Comm = 0 Then
            Exit Sub
         Else
            ActiveSheet.Unprotect
            Target.Value = Comm
            ActiveSheet.Protect
         End If
      End Select
   End If
End Sub

***Please Note Well...if this is a Shared File ALL BETS are off...

Hi Sinon05

Add the Line of Code as indicated.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim LR           As Long
   Dim Comm         As Double
   
   If Target.Cells.Count > 1 Then Exit Sub  '<---Add this line of Code
   
   LR = Range("Q" & Rows.Count).End(xlUp).Row
   If Not Intersect(Target, Range("Q2:Q" & LR)) Is Nothing Then
      Select Case Target.Value
      Case "Please input value"
         On Error Resume Next
         Application.DisplayAlerts = False
         Comm = Application.InputBox _
                (Prompt:="Please enter Commission.", _
                 Title:="ENTER COMMISSION", Type:=1)
         On Error GoTo 0
         Application.DisplayAlerts = True
         If Comm = 0 Then
            Exit Sub
         Else
            ActiveSheet.Unprotect
            Target.Value = Comm
            ActiveSheet.Protect
         End If
      End Select
   End If
End Sub

You're welcome...glad I could help. Thanks for the Rep.