jaxgev


Here's what I'm looking to do in Excel:

User inputs data in cell C7
A function in cell C11 calculates
A function in cell C12 calculates

I want to ensure that both C11 and C12 are positive based on C7 input
If they are not, I want a msgbox to appear saying, "Enter a Larger Value."

I used the Change event over the Calculate event because it's my understanding that the Calculate event will not work with functions.
Here's what I've got so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("C11:C12") < 0 then
MsgBox "Enter Larger Value."
End If
End Sub

Can anyone help




Re: Easy msgbox Query

Adamus Turner


If Sheet1.Range("C11").Value < 0 OR Sheet1.Range("C12").Value < 0 Then

Adamus







Re: Easy msgbox Query

spotty

Moved to VBA forum

Please ask questions related to VBA in this forum rather than the VB ones which are intended for VB.NET questions.






Re: Easy msgbox Query

jaxgev

Adamus,

Thank you for the reply.

I tried:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Sheet1.Range("C11").Value < 0 Or Sheet1.Range("C12").Value < 0 Then
MsgBox "Enter Larger Value."
End If
End Sub

And still get nothing. Ideas

Jackie





Re: Easy msgbox Query

jaxgev

Sorry about that.



Re: Easy msgbox Query

Adamus Turner

jaxgev wrote:

Adamus,

Thank you for the reply.

I tried:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Sheet1.Range("C11").Value < 0 Or Sheet1.Range("C12").Value < 0 Then
MsgBox "Enter Larger Value."
End If
End Sub

And still get nothing. Ideas

Jackie

You're using the wrong Event...here you go:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sheet1.Range("C11").Value < 0 Or Sheet1.Range("C12").Value < 0 Then
MsgBox "Enter Larger Value."
End If
End Sub

Adamus






Re: Easy msgbox Query

jaxgev

Still doesn't work. I'm not sure what I'm doing wrong.

Also, won't that run msgbox anytime a selection is made anywhere on the sheet I just want it to run when the selection is made in C7





Re: Easy msgbox Query

Adamus Turner

It will run the messagebox any time the IF statement is true.

I'm not sure what you're doing wrong either.

Adamus






Re: Easy msgbox Query

Andy Pope

Hi,

right click the sheet tab and pick View Code and paste the following.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("C7")) Is Nothing Then
If Range("C11") < 0 Or Range("C12") < 0 Then
MsgBox "Enter Larger Value."
End If
End If

End Sub