private sub needed

[juan1]

I would like a private sub written that does the following:
First a value that is being entered into A1 affects the answer to a formula in A2. I don't want the answer to the formula in A2 be less than 3.
If this occurs A1's input brings a message up and will not let that input happen.

This also happens in B1 effecting, B2 and C1 effecting C2

thanks for any help
Juan

 

[ranman256]

In the developer VBE, project left pane, click THISWORKBOOK,
insert the code as shown in image: (there is no image upload)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Offset(1, 0).Value < 3 Then
  MsgBox "Result in Row 2 cannot be less than 3"
  Target.Value = 99
End If
End Sub

 

[juan1]

Probably should have explained a little more, thought I could have improvised.
I don't think I can use offset because actually A1 effects A2, A3 & also A4. If any of those go under 3 the message needs to come up.

also could you add the same happening in column B


thanks again for the help

 

[Rick Rothstein]

...because actually A1 effects A2, A3 & also A4.

Can you explain this in a little more detail? If A2 contains a formula, how is A3 linked to it? I thought the user is typing the value into the cells... how is that working when the cell contains a formula?

 

[juan1]

A2, A3 & A4 are formulas that use an input from A1.
If A1's input effects any of those so the answer to the formulas goes below 3, A1 can not let the input happen.
thanks

 

[mikerickson]

Data Validation on A1, with the formula =(A$2>=3) should do what you want

 

[juan1]

where do I put in that is A1 where this takes place


thanks

 

[mikerickson]

Select A1 and then use Data Validation.

 

[juan1]

I meant in this code. back to the beginning of by request.
thanks

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Offset(1, 0).Value < 3 Then
MsgBox "Result in Row 2 cannot be less than 3"
Target.Value = 99
End If
End Sub