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

#### pcvchriskmg

##### Board Regular
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?

Chris

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### JamesW

##### Well-known Member
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

##### Well-known Member
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:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns(1)) Is Nothing Then
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
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

##### Well-known Member
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):

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Columns(1)) Is Nothing Then
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
End If
End Sub``````

Replies
1
Views
212
Replies
3
Views
90
Replies
6
Views
142
Replies
5
Views
83
Replies
10
Views
579

1,191,547
Messages
5,987,209
Members
440,085
Latest member
MBecker79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back