maksinx
03-12-2007, 10:21 AM
hello everyone,
i have a below codes which works fine individually and i would like add the code 2 into the code1 which both stated below but i have tried to put in a module and also tried to put at the bottom of the code1 and gave me a warning it seems i have done something wrong.What is the way to make two codes work in one worksheet.?
in brief i want to combine to codes into a one worksheet.
is it also possible to set up duplicate warning for two columns instead of two which is shown below.
thanks in advance
please advice
CODE1
Private Sub Backup_Click()
ActiveWorkbook.SendMail xxx@email.com (xxx@email.com)
End Sub
Private Sub CommandButton1_Click()
Calculate_Eta_Report_For_Next_Five_Days
End Sub
Private Sub CommandButton2_Click()
Call Delete_Report
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J:J" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
CODE2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("B:B"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("F:F"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
End Sub
i have a below codes which works fine individually and i would like add the code 2 into the code1 which both stated below but i have tried to put in a module and also tried to put at the bottom of the code1 and gave me a warning it seems i have done something wrong.What is the way to make two codes work in one worksheet.?
in brief i want to combine to codes into a one worksheet.
is it also possible to set up duplicate warning for two columns instead of two which is shown below.
thanks in advance
please advice
CODE1
Private Sub Backup_Click()
ActiveWorkbook.SendMail xxx@email.com (xxx@email.com)
End Sub
Private Sub CommandButton1_Click()
Calculate_Eta_Report_For_Next_Five_Days
End Sub
Private Sub CommandButton2_Click()
Call Delete_Report
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J:J" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
CODE2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("B:B"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("F:F"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
End Sub