|
creat a pop up box
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 |
creat a pop up box
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 |
creat a pop up box
I do not need these the same time
I just need the one I have for minimum and now I need one for a differnent row for maximum The maximum will be in column D 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 |
creat a pop up box
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 |
creat a pop up box
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 |
creat a pop up box
Hi,
Maybe something like this .... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim varVal As Variant If Target.Cells.Count 1 Then Exit Sub If IsNumeric(Target.Value) = False Then Exit Sub Call ToggleEvents(False) Select Case Target.Column Case 1 To 6 varVal = Target.Value With Application.WorksheetFunction If varVal < .Max(varVal, Me.Columns(Target.Column)) Then MsgBox "Not Max - column " & Choose(Target.Column, "A", "B", "C", "D", "E") Else MsgBox "Max - column " & Choose(Target.Column, "A", "B", "C", "D", "E") End If End With End Select Call ToggleEvents(True) End Sub Public Sub ToggleEvents(blnState As Boolean) '// Written by Zack Barresse, aka firefytr With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState = True Then .CutCopyMode = False .StatusBar = False End If End With End Sub HTH -- Regards, Zack Barresse, aka firefytr "delete automatically" wrote in message ... I do not need these the same time I just need the one I have for minimum and now I need one for a differnent row for maximum The maximum will be in column D 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 |
creat a pop up box
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 |
creat a pop up box
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 |
creat a pop up box
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 |
creat a pop up box
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 |
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 |
| All times are GMT +1. The time now is 10:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com