Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Conditional Locking Of Cells

1. I agreed to these rules
Join Date
1st September 2007
Posts
10

## Conditional Locking Of Cells

I have a condition whereby if cell C7 has a value entered, then cells D7 & E7 cannot have values entered. Like wise, if cells D7 & E7 have values, them cell C7 cannot. Cell F7 would be the product of the calculations using the data either in only C7 or only in D7 & E7. Please see the sample attached worksheet with my problem elaborated. Thanks so much for any help.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Conditional Locking Of Cells

The formula for F7 is

=IF(C7<>0, C7*B7, (D7 * B10) * 0.75 + (E7 * B10) * 0.25 )

for C7: Cell value is = 0 (and format w/ hatch pattern)
for D7 and C7: Formula is =\$C7<>0 (and format w/ hatch pattern).

3. ## Re: Conditional Locking Of Cells

Kenp7,

This was a duplicate thread, I have deleted the other one.

Do you want to actually lock the cells?

4. ## Re: Conditional Locking Of Cells

5. I agreed to these rules
Join Date
1st September 2007
Posts
10

## Re: Conditional Locking Of Cells

Originally Posted by Reafidy
Kenp7,

This was a duplicate thread, I have deleted the other one.

Do you want to actually lock the cells?

Yes, I need to lock C7 if D7 & E7 have values. Likewise, I need to lock D7 & E7 if C7 has a value. Thanks so much!

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Conditional Locking Of Cells

Assuming your sheet is protected and C7:E7 start off being empty and unlocked, then the following code should work. It will be defeated if a user enters values in more than one cell at a time. The code must be placed in the sheet module (right click on the sheet tab and select View Code).
VB:
```Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Me.Unprotect
With Target.Cells(1)
Range("D7:E7").Locked = (Len(.Value) > 0)
Range("C7").Locked = Not (IsEmpty(Range("D7")) And IsEmpty(Range("E7")))
End If
End With
Me.Protect
End Sub

```

7. I agreed to these rules
Join Date
1st September 2007
Posts
10

## Re: Conditional Locking Of Cells

I tried it and it worked only if I first entered a value in C7. If I start out with D7, it automatically (apparently) protected or locked that cell disallowing any value to be entered.
I pasted the code and double checked it for accuracy but no luck. Sorry if I failed to do something here.

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Conditional Locking Of Cells

I just now tried the macro again and it worked as it should even when I started with D7. Did you start with the three cells C7:E7 unlocked?

If it's still not working, please attache the workbook with the problem so it can be checked to see what went wrong.

9. I agreed to these rules
Join Date
1st September 2007
Posts
10

## Re: Conditional Locking Of Cells

Thanks for your continued patience. Attached please see my worksheet. Please understand, this worksheet will be used by my potential customers to compare one service with another and as such, will be making multiple changes, starting over, etc. The worksheet also needs to be protected so no one can make any code or formula changes. The protection password is "nutrient"

Excel Video Tutorials / Excel Dashboards Reports

10. Established Member
Join Date
14th July 2007
Posts
759

## Re: Conditional Locking Of Cells

It looks like the 3 cells in question (C7:E7) may need to be empty in order for this work. If all 3 are empty to start, the locking works as it should. If all 3 are set to 0 to start, it only works when changes are made to C7.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno