mercredi 1 novembre 2017

Excel -VBA help, Protect and Unprotect custom cells according to a list selection

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.

Argument not optional

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