I have a sheet that has a cell (G:22) with drop down choices if the user picks the drop down "MWD" then I want it to prompt you to change the answer in (O:15)
I tried this at the end of as existing change event.... all ready on the same sheet but the addition does nothing
Dim dummy
If ws.cells(22, 7) = "MWD" Then
dummy = MsgBox("Please show service interruption for daily email", vbExclamation, "Denied")
cells(15, 15).Select
End
End If
The code I put it at the end is
Private Sub Worksheet_Change(ByVal Target As Range)
Dim EndTime As Range
Dim SBlank As String
SBlank = """" & """"
Set EndTime = Range("B10:B47")
'ActiveSheet.Unprotect
'Application.EnableEvents = True
If Target.Column <> 2 Or Target.Count > 1 Or Target.Row < 10 Or Target.Row > 47 Then Exit Sub
If Not Application.Intersect(EndTime, Range(Target.Address)) Is Nothing Then
ActiveSheet.Unprotect
If Target.Value = "" Then
Target.Offset(1, -1).Value = ""
GoTo 99
End If
If Intersect(Target, Range("B10:B47")) Is Nothing Then Exit Sub
Dim xHour As String
Dim xMinute As String
Dim xWord As String
Application.EnableEvents = False
xWord = Format(Target.Value, "0000")
xHour = Left(xWord, 2)
xMinute = Right(xWord, 2)
On Error Resume Next
Target.Value = TimeValue(xHour & ":" & xMinute)
On Error Resume Next
Application.EnableEvents = True
If TimeValue(Format(Target.Value, "hh:mm")) <> "12:00:00 AM" Then
Target.Offset(1, -1).Value = "=IF(ISBLANK(B" & Target.Row & ")," & SBlank & ",B" & Target.Row & ")"
End If
End If
'ActiveSheet.Protect
'Application.EnableEvents = True
99:
Dim dummy
If ws.cells(22, 7) = "MWD" Then
dummy = MsgBox("Please show service interuption for daily email", vbExclamation, "Denied")
cells(15, 15).Select
End
End If
Application.EnableEvents = True
ActiveSheet.Protect
End Sub
Bookmarks