Howdo I password protect a list entry

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I am creating a spreadsheet to keep track of vacation time. I would
like to have a drop down list that employees could select there names
from and on any given work day. I would like to have a means of
making sure that employees can only select or remove themselves from
any day they select to take off hence the need to protect or validate
who is trying to make a change to what. There can only be 2 employees
off any given day per shift.
 
One way:

Assume days are in column, the "vacation cells are in rows 2 and 3,
that the cells have validation/List applied, and that the cells are
initially blank.

Put this in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim vaPWords As Variant
Dim vResult As Variant
Dim i As Long
Dim sPrompt As String
Dim bValid As Boolean

vaPWords = Array("Bob", "duck", "Carol", "goose", _
"Ted", "loon", "Alice", "swan")
sPrompt = "Enter your password:"
With Target
If Not Intersect(Range("B2:AE3"), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
If Not Application.CountIf(Cells(2, _
.Column).Resize(2, 1), .Value) > 1 Then
For i = 0 To UBound(vaPWords) Step 2
If .Value = vaPWords(i) Then
Do
vResult = Application.InputBox( _
Prompt:=sPrompt, _
Title:="Vacation", _
Default:="", _
Type:=2)
If vResult = False Then Exit For
If vResult = vaPWords(i + 1) Then
bValid = True
Else
sPrompt = "Wrong Password" & _
vbNewLine & _
"Enter your password:"
End If
Loop Until bValid
Exit For
End If
Next i
End If
If Not bValid Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End If
End If
End With
End Sub

Note: Since this uses validation, it can be overridden by pasting
into the cell. You can either restore the validation list via code
or use a different technique for selecting employees.
 
Back
Top