| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
creat a pop up box
Dave,
That worked perfectly, Thanks a million "Dave Peterson" wrote: Untested... Select E3:E33 With E3 the activecell =OR(ISNUMBER(E3)=FALSE,COUNTIF(E$3:E$33,"="&E3)1 ) delete automatically wrote: I think were almost there. In column e rows 3 thru 33 when they enter a number in a cell and its the max number in that row then have it say NEW RECORD. Thanks again "Dave Peterson" wrote: Maybe... Try this on a test worksheet Select column E With E1 the Activecell Data|Validation On the Settings tab: Allow: Custom formula: =OR(ISNUMBER(E1)=FALSE,COUNTIF(E:E,"="&E1)1) On the Error Alert tab: Style: Information Message: New Record delete automatically wrote: Dave, Would there be an easier way to do this, like using data validation? That way I could do each column seperatley and have differnent saying for each columns pop up box? "Dave Peterson" wrote: It seems different from what your original post wanted. Did you try to modify that earlier suggestion? Take a look at that, give it a try and post back if you have questions. delete automatically wrote: Actually what I need is, I have 6 columns (categorys) a thru f and in each category I would like to see NEW RECORD when they enter the maximum amount. So when they enter like 80 and thats the max in column a then say NEW RECORD and if they enter 50 in column b, and thats the max in that coumn then say NEW RECORD. Does that make sense to you? Thanks "Dave Peterson" wrote: Do you need both of these at the same time? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Variant 'one cell at a time If Target.Cells.Count 1 Then Exit Sub End If If Not (Intersect(Target, Me.Range("c:c")) Is Nothing) Then 'do column C Application.EnableEvents = False x = Target.Value Target.Value = "" If x < Application.WorksheetFunction.Min(Me.Range("c:c")) Then MsgBox "NEW RECORD" End If Target.Value = x Application.EnableEvents = True ElseIf Not (Intersect(Target, Me.Range("d:d")) Is Nothing) Then 'do column D Application.EnableEvents = False x = Target.Value Target.Value = "" If x Application.WorksheetFunction.Max(Me.Range("d:d")) Then MsgBox "NEW RECORD" End If Target.Value = x Application.EnableEvents = True End If End Sub You may want to remove the clearing, checking, and replacing with something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'one cell at a time If Target.Cells.Count 1 Then Exit Sub End If If IsNumeric(Target.Value) = False Then Exit Sub End If If IsEmpty(Target.Value) Then Exit Sub End If If Not (Intersect(Target, Me.Range("c:c")) Is Nothing) Then 'do column C If Application.CountIf(Me.Range("C:C"), "<" & Target.Value) = 0 Then MsgBox "NEW RECORD" End If ElseIf Not (Intersect(Target, Me.Range("d:d")) Is Nothing) Then 'do column D If Application.CountIf(Me.Range("d:d"), "" & Target.Value) = 0 Then MsgBox "NEW RECORD" End If End If End Sub But I'm not sure what you really want to do with ties. delete automatically wrote: I have a formula that when a cell in column C is the minimum it will say NEW RECORD. What I need now is if a cell in column D is the maximum the say NEW RECORD Here is the formula I have for the minimun in column c Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("c:c")) Is Nothing Then Exit Sub Application.EnableEvents = False x = Target: Target = "" If x < Application.WorksheetFunction.Min(Range("c:c")) _ Then MsgBox ("NEW RECORD") Target = x Application.EnableEvents = True End Sub I took min out and put max and changed the ("c:c")) to ("d:d")) but does not work anything else I need to do? Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pls help for creat a Macro.... | Excel Discussion (Misc queries) | |||
| Creat formulas | Excel Worksheet Functions | |||
| Creat | Excel Discussion (Misc queries) | |||
| How can I creat | Excel Discussion (Misc queries) | |||
| Creat a new worksheet | Excel Discussion (Misc queries) | |||
Threaded Mode
