loop through rows

[Kevin Carter]

hi
i have a worksheet that contains all the days of the year on each row
in the column i have employee names
i am creating a vacation tracker making sure that i have enought employees on shift
i have counted the number of blank cells in a range(s8)
if the value is different from 8 i want to lock the range of cells
leaving the cell with a value in it unlocked

i want to do this to stop more than one employee having vaction same day
but allowing the employee who has a vacation to change his/her mind

i have the following code that locks a range of cell


How can i adapt this to
1. loop through the rows
2 leave the cell with a value in it unlocked


Private Sub Worksheet_Change(ByVal Target As Range)
If [s8] <> 7 Then
ActiveSheet.Unprotect
Range("e8:k8").Locked = True
ActiveSheet.Protect
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect
Range("e8:k8").Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect
End If
End Sub

thanks

kevin

 

[Ron Rosenfeld]

hi
i have a worksheet that contains all the days of the year on each row
in the column i have employee names
i am creating a vacation tracker making sure that i have enought employees on shift
i have counted the number of blank cells in a range(s8)
if the value is different from 8 i want to lock the range of cells
leaving the cell with a value in it unlocked

i want to do this to stop more than one employee having vaction same day
but allowing the employee who has a vacation to change his/her mind

i have the following code that locks a range of cell


How can i adapt this to
1. loop through the rows
2 leave the cell with a value in it unlocked


Private Sub Worksheet_Change(ByVal Target As Range)
If [s8] <> 7 Then
ActiveSheet.Unprotect
Range("e8:k8").Locked = True
ActiveSheet.Protect
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect
Range("e8:k8").Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect
End If
End Sub

thanks

kevin

I don't follow your logic/setup, but the following is a suggestion, annotated well enough that I hope you can modify it to your requirements. It did seem to me, from your description, that cells with values should always be unlocked. If that is not the case, you can move the test for values "into the loop" and test on a row by row basis.

=========================
Option Explicit
Sub LockVacation()
Dim rg As Range
Dim rw As Range, c As Range

'denotes column of cell to be tested if have enough employees
Const cTest As Long = 9 'I'm not sure what column in your data

'set rg to the range including employee entries
Set rg = Range("B2:F200")

'unprotect and lock everything
ActiveSheet.Unprotect
rg.Locked = True

'unlock all cells with values
'On error in case no cells are found
On Error GoTo NoCellsFound
rg.SpecialCells(xlCellTypeConstants).Locked = False

'Check each row
For Each rw In rg.Rows

'check to see if blanks need to be unlocked
If Cells(rw.Row, cTest).Value <> 7 Then
rw.SpecialCells(xlCellTypeBlanks).Locked = False
End If
Next rw
ActiveSheet.Protect
Exit Sub

NoCellsFound:
If Err.Number = 1004 Then
Resume Next
Else
MsgBox ("Error in Macro: " & Err.Number & vbTab & Err.Description)
End If
ActiveSheet.Protect
End Sub
================================

 

[Kevin Carter]

thanks