|
Posted to microsoft.public.excel.misc
|
|
|
Prevent Duplicate Records
I'm not a very good programmer (yet!).
--
Biff
Microsoft Excel MVP
"Kenny" wrote in message
...
Thanks for all your help. Can you help me with my other post.
I would like to simplify my command button click procedures. I want to
call a
sub procedure from each click event. The click event will set a variable
to
the command button I want disabled. The called sub will first endable all
the
command buttons and then disable the one that I chose. Please see my
example
of simplify below. my current code will follow.
EXAMPLE OF SIMPLIFY NOT WORKING:
Option Explicit
Dim Choice
Sub CommandButtonEnable()
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Choice.Enabled = False
End Sub
Private Sub ActiveRecords_Click()
Choice=ActiveRecords
End Sub
Private Sub ReportPreview_Click()
Choice=ReportPreview
End SUb
EXSISTING CODE:
Option Explicit
Sub SortThisSheet()
Dim LastRow As Long
Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub
'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("D:D"), .Cells) Is Nothing Then
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End If
End With
'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If
End Sub
Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd,
Criteria2:="<"
End Sub
Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub
Was this post helpful
"T. Valko" wrote:
That just prevents duplicates. This application is for duplicates with a
condition.
--
Biff
Microsoft Excel MVP
"Chip Pearson" wrote in message
...
You can do this with data validation *if* the data entered in column A
is
entered from the top down.
You can do it even if the values are entered into column A in an
arbitrary
manner; there is no need for the "top down" restriction. Suppose you
can
safely say that ALL the data will be entered somewhere within the range
A1:A20. Even if you don't use all the cells in that range, you can be
sure
than no data will be entered in other rows. Given that, select A1:A20,
go
to the Data menu, choose Validation, and then Custom in the Allow list.
There, enter the formula:
=COUNTIF($A$1:$A$20,A1)=1
This will prevent duplicates in A1:A20 regardless in the order in which
they are ordered.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"T. Valko" wrote in message
...
You can do this with data validation *if* the data entered in column A
is
entered from the top down. For example, you enter an account number in
A1
then A2 then A3 etc. It won't work if you enter the data randomly like
in
A10 then A2.
Interested in that?
--
Biff
Microsoft Excel MVP
"Kenny" wrote in message
...
I have a spreadsheet Column A is the account number. I want to write a
procedure that if the user enters the same account number in column a
that
already exsits and that exsisting record has nothing in column L then
it
clears the cell and msgbox them record already exsists. If column l
of
the
exsisting record does contain data it will allow them to add the
account
number over again. Here is my code and I have not started this
procedure
anywhere in this code. Thanks!
Option Explicit
Sub SortThisSheet()
Dim LastRow As Long
Application.EnableEvents = False
With Me
If .FilterMode Then
.ShowAllData
End If
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("a5:L" & LastRow)
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
End With
End With
Application.EnableEvents = True
'AutoFilterMode = False
End Sub
'charge off date date()+10
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count 1 Then Exit Sub
If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 2).ClearContents
Else
With .Offset(0, 2)
.Value = Date + 10
End With
End If
Application.EnableEvents = True
End If
End With
'pick data from code sheet for rescode
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 9 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If (IsError(Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0))) Then
Target.Value = ""
Else
Target.Value = Application.VLookup(Target.Value,
Worksheets("Codes").Range("CodeData"), 2, 0)
End If
Application.EnableEvents = True
End If
End Sub
Private Sub ActiveRecords_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = False
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Band3_Click()
Application.ScreenUpdating = False
Band3.Enabled = False
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 3"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Band4_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = False
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 4"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Band5_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = False
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Band 5"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Manager_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = False
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Manager"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub Military_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = False
ActiveRecords.Enabled = True
ReportPreview.Enabled = True
Call SortThisSheet
Range("A5").AutoFilter Field:=3, Criteria1:="Military"
Range("A5").AutoFilter Field:=12, Criteria1:="="
Application.ScreenUpdating = True
End Sub
Private Sub ReportPreview_Click()
Application.ScreenUpdating = False
Band3.Enabled = True
Band4.Enabled = True
Band5.Enabled = True
Manager.Enabled = True
Military.Enabled = True
ActiveRecords.Enabled = True
ReportPreview.Enabled = False
Call SortThisSheet
'Range("A5:L5").AutoFilter
Range("A5").AutoFilter Field:=3
Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd,
Criteria2:="<"
End Sub
Private Sub DailyReport_Click()
Worksheets("Tracker").Range("A5").AutoFilter Field:=3
ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent",
Operator:=xlAnd, Criteria2:="<"
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls"
Windows("New Tracker.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues
Windows("New Tracker.xls").Activate
Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy
Windows("Daily Tracker Report.xls").Activate
Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
Worksheets("sheet1").Range("A5").Select
Windows("New Tracker.xls").Activate
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Selection.EntireRow.Delete
'Windows("Daily Tracker Report.xls").Activate
Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent"
Application.ScreenUpdating = True
Call ActiveRecords_Click
End Sub
|