Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Howdo I password protect a list entry
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Howdo I password protect a list entry
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. In article , (Bob) wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I protect all excel tabs in a file with one password entry? | Excel Discussion (Misc queries) | |||
LIST ENTRY ENABLE DROP DOWN LIST TO ACCEPT MORE THAN 1 ENTRY | Excel Discussion (Misc queries) | |||
how to password protect a validation list? | Excel Discussion (Misc queries) | |||
Password Protect | Excel Worksheet Functions | |||
password protect | Excel Discussion (Misc queries) |