Locking cells in Microsoft Excel

C

Curtis

I am not an expert at excel, but I want to lock certain cells, once the value
of a different cell that makes reference in a formula to these cells reaches
0. Can this be done and can someone tell me how.
 
I

Ian

Right click on the sheet tab and select View Code, then paste the routine
below into the VBA window (where the falshing cursor is).

Change the sheet names and cell references to suit your requirements.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").Unprotect
If Range("A1") = 0 Then
Range("B1").Locked = True
Range("C2:C3").Locked = True
Else
Range("B1").Locked = False
Range("C2:C3").Locked = False
End If
Worksheets("Sheet1").Protect
End Sub
 
C

Curtis

I can't get the formula you sent me to work. What I want to do is lock cells
H9 to GV9 once the value of cell G9 becomes 0 so that a user of this
worksheet cannot oversell a product. A number an be entered into the cells
H9 to GV9 to tell the spreadsheet user how many of each product a client
needs. The cell G9 refers to the number of product left in inventory. As
the user enters #'s into any of these cells, the number of inventory
remaining decreases until it get to zero, but right now you can sell infinite
product because the value of G9 can go into negative numbers. When I use the
formula that you gave me, it always comes back with errors. The name of the
worksheet according to the view code is Sheet 1(Inventory). What am I doing
wrong. Can I also make it so that I cannot enter a value in any of the cells
that would make G9 lower than 0 and show a message when a user tries to do
this, prompting them to change the # or continue anyways.
 
Top