Possible to unlock specific cells with IF statement or evaluation of variable?

[pcvchriskmg]

Good Morning,

I'm helping a colleague with some expense report issues and I think Excel can help systematically solve some of the problems.

In a nutshell, people are trying to claim expenses when they are not allowed to.

For example, people can only claim expense 'X' if they have traveled 'Y' miles.

My idea is that the user will need to enter in the number of miles traveled. Based on that number, Excel will "unlock" a cell (H3 for example) where the person can then enter a value to claim an expense. It would look like this:

User enters miles traveled in cell 'A1" - User enters '20' for example

"IF statement" evaluates the value '20' against value in cell 'B1' - '50' for example (number of miles user must travel in order to claim expense) - Excel determines that 20 is less than Y (50)

Excel does not unlock cell 'H3" and therefore user cannot enter in value.

User changes miles traveled to '60' (in cell 'A1') 60 > 50, so "IF statement" unlocks cell 'H3' and user can enter a value into cell 'H3' to claim the expense.

I have two questions: 1) Is what I have described possible? Basically, an IF statement being able to unlock cells. 2) For the problem I initially described, is there an easier way to design the solution in Excel?

Thanks in advance,
Chris

 

[JamesW]

Hi there,

You could use data validation:

=A1 > =50.

The problem with this though is that it can be fooled (user enters 60 miles and £50 expense claim, then changes 60 miles to 10 miles - the expense claim is still there), so you would need another bit of validation in your mileage cell:

=IF(AND(A1 > =50,H3 < > ""),1,0)

Let me know if this is helps.

 

[Airfix9]

Locking and unlocking cells can't be accomplished with an IF statement but it can be done with some VBA.

Assuming, like most expense claim forms, the miles travelled entry can be entered in any cell in a specific column, to use your example column A, you can use a worksheet event procedure to unlock the correct cell and even give a message if the value entered is not claimable.

Use this code, edit to suit, entered in the appropriate sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Unprotect "password"
        If Target.Value < 50 Then
            Target.Offset(, 1).Locked = True
            MsgBox "You can't claim for this"
        Else
            Target.Offset(, 1).Locked = False
        End If
        Protect "password"
    End If
End Sub

Remember to password protect your VBA code though, to prevent users from finding out the password used to protect the sheet. You would also have to pre-unlock column A so that an entry can be made in it in the first place.

An alternative is to use a data validation on the cells in column A so that the user can't enter less than the required number (in this case 50). The advantage of the first method is that they can enter the number, whatever it is, so that you can see how many miles have been travelled, even if they can't claim them, the latter will only accept values equal to or greater than the required level.

Choose the method which suits you best.

Hope that helps.

 

[Airfix9]

The problem with this though is that it can be fooled (user enters 60 miles and £50 expense claim, then changes 60 miles to 10 miles - the expense claim is still there)...

Didn't foresee that! This would help there (code method):

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Unprotect "password"
        If Target.Value < 50 Then
            Target.Offset(, 1).ClearContents
            Target.Offset(, 1).Locked = True
            MsgBox "You can't claim for this"
        Else
            Target.Offset(, 1).Locked = False
        End If
        Protect "password"
    End If
End Sub