Right-click on the sheet tab and paste this in the window that opens:
![]()
Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim cell As Range Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R"))) If r Is Nothing Then Exit Sub For Each cell In r With cell If .Column = Columns("P").Column Then If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then With Cells(.Row, "R") .ClearContents .Select MsgBox "Must be blank.", vbOKOnly End With End If Else ' it's in column R If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then .ClearContents .Select MsgBox "Must be blank.", vbOKOnly End If End If End With Next cell End Sub
Its not working, when I insert it into the field, it tells me "Ambugious name detected:Worksheet_Change"
Your part is towards the end....
This is how it looks:
![]()
Private Sub Worksheet_Activate() End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'this is all for validation of user input on the details sheet 'make sure a date has been input in E11 and E12 If Target.Cells(1, 1).Address(0, 0) = "E11" Then If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then MsgBox "You must input a date!", vbExclamation, "DATE REQUIRED" Target.Cells(1, 1) = "" Exit Sub End If End If If Target.Cells(1, 1).Address(0, 0) = "E12" Then If Target.Cells(1, 1) <> "" And Not IsDate(Target.Cells(1, 1)) Then MsgBox "You must input a valid date!", vbExclamation, "DATE REQUIRED" Target.Cells(1, 1) = "" Exit Sub End If End If 'make sure enddate>=startdate If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then If Sheets("Salary Detail").Range("enddate") < Sheets("Salary Detail").Range("startdate") Then MsgBox "We've heard of short projects before, but this one really takes the cake!", vbExclamation, "END DATE BEFORE START DATE" Sheets("Salary Detail").Range("enddate") = "" Exit Sub End If End If 'make sure enddate<=startdate + 366 If Sheets("Salary Detail").Range("enddate") <> "" And Sheets("Salary Detail").Range("startdate") <> "" Then If Sheets("Salary Detail").Range("enddate") > Sheets("Salary Detail").Range("startdate") + 366 Then MsgBox "This project has a very long initial year!", vbExclamation, "END DATE BEFORE START DATE" Sheets("Salary Detail").Range("enddate") = "" Exit Sub End If End If 'make sure FTE's in C23:C62 and the levels of effort in H23:H62 are in the range 0-1 If Target.Column = 3 Or Target.Column = 8 Then If Target.Row >= 23 And Target.Row <= 62 Then If Not IsNumeric(Target.Cells(1, 1)) Then MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED" Target.Cells(1, 1) = "" Exit Sub ElseIf Target.Cells(1, 1) < 0 Or Target.Cells(1, 1) > 1 Then MsgBox "You must enter a number between 0 and 1!", vbExclamation, "NUMBER BETWEEN 0 AND 1 REQUIRED" Target.Cells(1, 1) = "" Exit Sub End If End If End If 'make sure the salaries in E23:E62 are positive numbers If Target.Column = 5 Then If Target.Row >= 23 And Target.Row <= 62 Then If Not IsNumeric(Target.Cells(1, 1)) Then MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED" Target.Cells(1, 1) = "" Exit Sub ElseIf Target.Cells(1, 1) < 0 Then MsgBox "You're getting paid that badly!?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED" Target.Cells(1, 1) = "" Exit Sub End If End If End If 'make sure the fringe benefit types in P23:P62 are appropriate If Target.Column = 16 Then If Target.Row >= 23 And Target.Row <= 62 Then If Target.Cells(1, 1) <> "" And Target.Cells(1, 1) <> "F" And Target.Cells(1, 1) <> "U" And Target.Cells(1, 1) <> "A" And Target.Cells(1, 1) <> "P" And Target.Cells(1, 1) <> "T" And Target.Cells(1, 1) <> "R" And Target.Cells(1, 1) <> "S" And Target.Cells(1, 1) <> "W" And Target.Cells(1, 1) <> "f" And Target.Cells(1, 1) <> "a" And Target.Cells(1, 1) <> "p" And Target.Cells(1, 1) <> "t" And Target.Cells(1, 1) <> "r" And Target.Cells(1, 1) <> "u" And Target.Cells(1, 1) <> "s" And Target.Cells(1, 1) <> "w" Then MsgBox "You must enter a fringe benefit code from the list above!", vbExclamation, "'F','A','P','T','R','S', OR 'W' REQUIRED" Target.Cells(1, 1) = "" Exit Sub End If End If End If If Target.Row >= 23 And Target.Row <= 62 Then 'make sure the project duration in L10 is a positive number If Target.Column = 12 And Target.Row = 10 Then If Not IsNumeric(Target.Cells(1, 1)) Then MsgBox "You must enter a number!", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED" Target.Cells(1, 1) = "" Exit Sub ElseIf Target.Cells(1, 1) < 0 Then MsgBox "Are you sure the project is that short?", vbExclamation, "NUMBER GREATER THAN 0 REQUIRED" Target.Cells(1, 1) = "" Exit Sub End If End If End If Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim cell As Range Set r = Intersect(Target, Rows("23:62"), Union(Columns("P"), Columns("R"))) If r Is Nothing Then Exit Sub For Each cell In r With cell If .Column = Columns("P").Column Then If .Value = "U" And Not IsEmpty(Cells(.Row, "R").Value) Then With Cells(.Row, "R") .ClearContents .Select MsgBox "Must be blank.", vbOKOnly End With End If Else ' it's in column R If Cells(.Row, "P").Value = "U" And Not IsEmpty(.Value) Then .ClearContents .Select MsgBox "Must be blank.", vbOKOnly End If End If End With Next cell End Sub
Last edited by shg; 08-20-2008 at 05:56 PM. Reason: deleted spurious quote
1. I meant to say this before: Please quit quoting whole posts. It just clutters the forum.
2. Please read the Forum Rules and then edit your post to wrap your code with Code Tags.
3. You can only have one Worksheet_Change event.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks