| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pop up box
I would like a box to pop up saying NEW RECORD.
In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pop up box
Put this in sheets code module
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: I would like a box to pop up saying NEW RECORD. In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pop up box
When I copied and paste the formula the If x <
Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") was red. Then when I tried it gave me a compile error and Private Sub Worksheet_Change(ByVal Target As Range) was highlited yellow. Any ideas? "excelent" wrote: Put this in sheets code module Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: I would like a box to pop up saying NEW RECORD. In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pop up box
You have been hit by wordwrap in the posting that's why the red text appears
Try this revision pasted with the line continuation mark "_" added. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) _ Then MsgBox ("NEW Record ") Target = x Application.EnableEvents = True End Sub BTW...........it is not a formula, it is event code. Gord Dibben MS Excel MVP On Sat, 20 Oct 2007 09:58:04 -0700, delete automatically wrote: When I copied and paste the formula the If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") was red. Then when I tried it gave me a compile error and Private Sub Worksheet_Change(ByVal Target As Range) was highlited yellow. Any ideas? "excelent" wrote: Put this in sheets code module Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: I would like a box to pop up saying NEW RECORD. In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pop up box
I ges it's the usual formatting problems in here try:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) _ Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: When I copied and paste the formula the If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") was red. Then when I tried it gave me a compile error and Private Sub Worksheet_Change(ByVal Target As Range) was highlited yellow. Any ideas? "excelent" wrote: Put this in sheets code module Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: I would like a box to pop up saying NEW RECORD. In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pop up box
Thank you, that worked perfectly
"excelent" wrote: I ges it's the usual formatting problems in here try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) _ Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: When I copied and paste the formula the If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") was red. Then when I tried it gave me a compile error and Private Sub Worksheet_Change(ByVal Target As Range) was highlited yellow. Any ideas? "excelent" wrote: Put this in sheets code module Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub "delete automatically" skrev: I would like a box to pop up saying NEW RECORD. In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pop up box
This can be done using data validation.
Suppose the range of interest is F1:F10 Select the range F1:F10 Goto DataValidation Allow: Custom Formula: =F1<MIN(F$1:F$10) Click the Error Alert tab Check: Show error alert...... Style: Information Type in a message like: New Record OK out -- Biff Microsoft Excel MVP "delete automatically" wrote in message ... I would like a box to pop up saying NEW RECORD. In column F if the cell is the minimun number then say NEW RECORD For example F1 has 200 F2 has 199 F3 has 144 so now If I put 140 in F4 a box appears sayin new record but if I put 150 in cell A4 then nothing is this possible? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
