Lock cell from editing if a cell in another sheet has a value

[GamerNeelie]

I have a forecasting table for sales over a 4 week period

Once the sales figures have been recorded and saved on another worksheet the box on the forecast turns yellow to show them sales have been processed (Value is greater than 0)

I need to be able to lock this cell so the user cant go back and edit the forecast for a previous day to give them better forecasting accuracy (Cheating )

The table is on "Home Page" or "Sheet2" and week 1 range is "J11:P11" [Sunday to Saturday Horizontal]

The linked table that I am recording the actual sales is on "Week1" or "Sheet7" Ranges "C6:C12" [Sunday to Saturday Vertical]

How can I lock Cell "J11" on Home Page if the value in cell "C6" on Sheet 7 is greater than 0?

Thanks.

 

[offthelip]

You could put this in the worksheet code for shee2 which prevents the user from selecting J11, which makes it difficult to change it. ( if you disable macros it won't work!!)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("J11:J11")) Is Nothing Then
   If Worksheets("Sheet7").Range("C6") > 0 Then
   Range("J12").Select
   End If
End If

End Sub

 

[GamerNeelie]

You could put this in the worksheet code for shee2 which prevents the user from selecting J11, which makes it difficult to change it. ( if you disable macros it won't work!!)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("J11:J11")) Is Nothing Then
   If Worksheets("Sheet7").Range("C6") > 0 Then
   Range("J12").Select
   End If
End If

End Sub

Getting a debug error on "If Worksheets("Sheet7").Range("C6") > 0 Then"

 

[offthelip]

Check your spelling of "sheet7" the code works perfectly well on my machine, what is the error you are getting. What have you got in C6 on Sheet 7

 

[GamerNeelie]

Check your spelling of "sheet7" the code works perfectly well on my machine, what is the error you are getting. What have you got in C6 on Sheet 7

C6 on Sheet 7 is an empty cell until a number is saved in that cell

I copied and pasted the code from the code you posted

 

[offthelip]

That means you do NOT have a sheet called "Sheet7" you must put the correct name, is it "sheet 7" or "Sheet7" or "Sheet 7"

 

[GamerNeelie]

That means you do NOT have a sheet called "Sheet7" you must put the correct name, is it "sheet 7" or "Sheet7" or "Sheet 7"

Ok I got it working, the sheet isn't called "sheet7" its called "week1"
sheet 7 is the name on the sheet list on VBA editor "Sheet7 (Week1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("J11:J11")) Is Nothing Then
   If Worksheets("Week1").Range("C6") > 0 Then
   Range("J12").Select
   End If
End If

End Sub

Thank you so much. Been a headache trying to figure that out

 

[GamerNeelie]

So that function works fine for Cell J11

When i tried to replicate that for the next cell on the list (K11) and it doesn't work?

'Sunday Week1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("J11:J11")) Is Nothing Then
   If Worksheets("Week1").Range("C6") > 0 Then
   Range("J12").Select
   End If
End If

End Sub
'Monday Week1
Private Sub Worksheet_SelectionChange2(ByVal Target As Range)
If Not Intersect(Target, Range("K11:K11")) Is Nothing Then
   If Worksheets("Week1").Range("C7") > 0 Then
   Range("K12").Select
   End If
End If

End Sub

where am I going wrong?

I had to change the sub name to "Worksheet_SelectionChange2" as it was giving me an Ambiguous name detected error?

 

[GamerNeelie]

Ok after some playing about i released i need to have them all in the same sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("J11:J11")) Is Nothing Then
   If Worksheets("Week1").Range("C6") > 0 Then
   Range("J12").Select
   End If
End If

If Not Intersect(Target, Range("K11:K11")) Is Nothing Then
   If Worksheets("Week1").Range("C7") > 0 Then
   Range("K12").Select
   End If
End If

End Sub

 

[GamerNeelie]

Thank you offthelip for your help. been trying multiple ways to get that working for a while now.

so happy I signed up to this forum. so many helpful people full of knowledge