Hi,

I have a spreadsheet that I would like an message to appear that says 'Have
you completed the forward cover form' if the value in the cell equals more
than $50,000. Is there some code for this?

Thanks

=If(A2>50000,"put the text here","")

Nikki wrote:
> Hi,
>
> I have a spreadsheet that I would like an message to appear that says 'Have
> you completed the forward cover form' if the value in the cell equals more
> than $50,000. Is there some code for this?
>
> Thanks

Thanks that formula works but I want a box to pop up that the user needs to
click ok to acknowledge it instead of it just being written in a cell.

Any ideas?

"Equiangular" wrote:

> =If(A2>50000,"put the text here","")
>
> Nikki wrote:
> > Hi,
> >
> > I have a spreadsheet that I would like an message to appear that says 'Have
> > you completed the forward cover form' if the value in the cell equals more
> > than $50,000. Is there some code for this?
> >
> > Thanks
>

Nikki

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myrange As String = "A1:A10"
On Error GoTo stoppit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(myrange)) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value > 50000 Then
MsgBox "Have you completed the forward cover form?"
End If
End If
stoppit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

myrange can be changed to suit.


Gord Dibben MS Excel MVP


On Thu, 24 May 2007 14:41:00 -0700, Nikki <(E-Mail Removed)>
wrote:

>Thanks that formula works but I want a box to pop up that the user needs to
>click ok to acknowledge it instead of it just being written in a cell.
>
>Any ideas?
>
>"Equiangular" wrote:
>
>> =If(A2>50000,"put the text here","")
>>
>> Nikki wrote:
>> > Hi,
>> >
>> > I have a spreadsheet that I would like an message to appear that says 'Have
>> > you completed the forward cover form' if the value in the cell equals more
>> > than $50,000. Is there some code for this?
>> >
>> > Thanks
>>

Hi,

Thanks. I changed myrange to I10:I15 but no message pops up.

"Gord Dibben" wrote:

> Nikki
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Const myrange As String = "A1:A10"
> On Error GoTo stoppit
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(myrange)) Is Nothing Then
> If IsNumeric(Target.Value) And Target.Value > 50000 Then
> MsgBox "Have you completed the forward cover form?"
> End If
> End If
> stoppit:
> Application.EnableEvents = True
> End Sub
>
> This is event code. Right-click on the sheet tab and "View Code".
>
> Copy/paste into that sheet module.
>
> myrange can be changed to suit.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Thu, 24 May 2007 14:41:00 -0700, Nikki <(E-Mail Removed)>
> wrote:
>
> >Thanks that formula works but I want a box to pop up that the user needs to
> >click ok to acknowledge it instead of it just being written in a cell.
> >
> >Any ideas?
> >
> >"Equiangular" wrote:
> >
> >> =If(A2>50000,"put the text here","")
> >>
> >> Nikki wrote:
> >> > Hi,
> >> >
> >> > I have a spreadsheet that I would like an message to appear that says 'Have
> >> > you completed the forward cover form' if the value in the cell equals more
> >> > than $50,000. Is there some code for this?
> >> >
> >> > Thanks
> >>
>
>

Nikki

Is the value a calculated value or typed in?

The Change code is triggered by typing, editing or copying to that cell.

Re-calculation will not trigger a change event.

My skills don't extend to making a pop-up using Calculate event code.

Wait for a better answer if you need a Calculate event


Gord

On Thu, 24 May 2007 17:06:01 -0700, Nikki <(E-Mail Removed)>
wrote:

>Hi,
>
>Thanks. I changed myrange to I10:I15 but no message pops up.
>
>"Gord Dibben" wrote:
>
>> Nikki
>>
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> Const myrange As String = "A1:A10"
>> On Error GoTo stoppit
>> Application.EnableEvents = False
>> If Not Intersect(Target, Me.Range(myrange)) Is Nothing Then
>> If IsNumeric(Target.Value) And Target.Value > 50000 Then
>> MsgBox "Have you completed the forward cover form?"
>> End If
>> End If
>> stoppit:
>> Application.EnableEvents = True
>> End Sub
>>
>> This is event code. Right-click on the sheet tab and "View Code".
>>
>> Copy/paste into that sheet module.
>>
>> myrange can be changed to suit.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Thu, 24 May 2007 14:41:00 -0700, Nikki <(E-Mail Removed)>
>> wrote:
>>
>> >Thanks that formula works but I want a box to pop up that the user needs to
>> >click ok to acknowledge it instead of it just being written in a cell.
>> >
>> >Any ideas?
>> >
>> >"Equiangular" wrote:
>> >
>> >> =If(A2>50000,"put the text here","")
>> >>
>> >> Nikki wrote:
>> >> > Hi,
>> >> >
>> >> > I have a spreadsheet that I would like an message to appear that says 'Have
>> >> > you completed the forward cover form' if the value in the cell equals more
>> >> > than $50,000. Is there some code for this?
>> >> >
>> >> > Thanks
>> >>
>>
>>

nikki -

while a pop-up message box is good, if the user can just click "ok" &
then go merrily along their way, what good is it?

somtimes for something like this i prefer a formatted merged-cell box
off to the side that shows up with conditional formatting when a value
goes higher than it's supposed to. that way it doesn't disappear
UNTIL they fix the problem.

for instance, in a worksheet i designed for my boss, in one biggish
merged cell that is VISIBLE TO THE USER i put the following formula

=IF((SUM(C3:C13)>6),"WARNING! THERE ARE ONLY SIX UNITS TO
DISTRIBUTE!","")

then i put in the conditional formatting:

if cell value is equal to ="WARNING! THERE ARE ONLY
SIX UNITS TO DISTRIBUTE!" formatting (i chose black background w/
red text - very noticeable)

so in playing with her numbers, if she goes over 6 units, that black
"box" with red text pops up, & won't go away until she fixes the
problem.

just an idea

susan



On May 24, 8:36 pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> Nikki
>
> Is the value a calculated value or typed in?
>
> The Change code is triggered by typing, editing or copying to that cell.
>
> Re-calculation will not trigger a change event.
>
> My skills don't extend to making a pop-up using Calculate event code.
>
> Wait for a better answer if you need a Calculate event
>
> Gord
>
> On Thu, 24 May 2007 17:06:01 -0700, Nikki <N...@discussions.microsoft.com>
> wrote:
>
>
>
> >Hi,
>
> >Thanks. I changed myrange to I10:I15 but no message pops up.
>
> >"Gord Dibben" wrote:
>
> >> Nikki
>
> >> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >> Const myrange As String = "A1:A10"
> >> On Error GoTo stoppit
> >> Application.EnableEvents = False
> >> If Not Intersect(Target, Me.Range(myrange)) Is Nothing Then
> >> If IsNumeric(Target.Value) And Target.Value > 50000 Then
> >> MsgBox "Have you completed the forward cover form?"
> >> End If
> >> End If
> >> stoppit:
> >> Application.EnableEvents = True
> >> End Sub
>
> >> This is event code. Right-click on the sheet tab and "View Code".
>
> >> Copy/paste into that sheet module.
>
> >> myrange can be changed to suit.
>
> >> Gord Dibben MS Excel MVP
>
> >> On Thu, 24 May 2007 14:41:00 -0700, Nikki <N...@discussions.microsoft.com>
> >> wrote:
>
> >> >Thanks that formula works but I want a box to pop up that the user needs to
> >> >click ok to acknowledge it instead of it just being written in a cell.
>
> >> >Any ideas?
>
> >> >"Equiangular" wrote:
>
> >> >> =If(A2>50000,"put the text here","")
>
> >> >> Nikki wrote:
> >> >> > Hi,
>
> >> >> > I have a spreadsheet that I would like an message to appear that says 'Have
> >> >> > you completed the forward cover form' if the value in the cell equals more
> >> >> > than $50,000. Is there some code for this?
>
> >> >> > Thanks- Hide quoted text -
>
> - Show quoted text -