Hi everyone I am trying to create a customisable spreadsheet.
So that if the user selects from a data validation list "N/A" (list is located in cell N18) a certain range of field become greyed out, and locked so that the user can not input anything into this range.
If the user selects the blank option from the list " " the user can input the information into needed in this range.
The user needs to be able to toggle from between theses values in the list and spreadsheet would need to be able to update itself each time by clicking a button on the spreadsheet.
When I click the button on the excel spreadsheet I get an Argument not optional error'
My code and error message is shown down below. Any help would be much appreciated.
Dim boolLock As Boolean
Dim r As Integer
Dim ws As Worksheet
Public Sub LockUnlockFields(ByVal Target As Range)
'Fields are shaded and locked acccording to user input, t9o creat a cust UI
'Target cell range
If Intersect(Target, Range("rESOne")) Then
'If Sheets("Nameplate").Range("rMotBool").Value = True
Application.EnableEvents = False 'Stops events from proccessing
Select Case Target.Value
Case Is = "N/A"
boolLock = True
r = 191
Debug.Print ("Trigger 1 ")
Case Is = " "
boolLock = False
Application.Wait (Now + TimeValue("0:00:01"))
Debug.Print ("Trigger 2 ")
End Select
ws.Unprotect Password:="myPassword"
With Range("K26:N32")
'fills the cells grey before locking them, or turn them back to original colour if it's unlocked
.Interior.Color = RGB(r, r, r)
.Locked = boolLock
End With
ws.Protect Password:="myPassword"
Application.EnableEvents = True
End If
End Sub
Thanks
Aucun commentaire:
Enregistrer un commentaire