Combining multiple Worksheet Change Events

[Milos]

Hi all,

I have two worksheet changes that I am having trouble to combine so that the changes work at the same time. The two changes do affect the same cells, but one hides the whole row and one stops specific cells from being deleted.

Here are the two codes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F28:J36,F45:J52,F58:J59")) Is Nothing Then Exit Sub
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    If Not IsDate(Target(1)) Then
        Application.Undo
        MsgBox " You can't delete contents from this cell." _
        , vbCritical, "Message alert!"
    End If
  
ExitPoint:
    Application.EnableEvents = True
End Sub

  Rows("28:36").Hidden = LCase(Range("B5").Value) = 1
  Rows("45:52").Hidden = LCase(Range("B5").Value) = 1
  Rows("58:59").Hidden = LCase(Range("B6").Value) = "no"

Can somebody please help me combine the two codes to work in cohesion?

Thanks,

 

[RickXL]

Hi,

I am not sure I am fully understanding the problem but does this help?

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    If Not Intersect(Target, Range("F28:J36,F45:J52,F58:J59")) Is Nothing Then
        If Not IsDate(Target(1)) Then
            Application.Undo
            MsgBox " You can't delete contents from this cell." _
            , vbCritical, "Message alert!"
        End If
    End If
    Rows("28:36").Hidden = LCase(Range("B5").Value) = 1
    Rows("45:52").Hidden = LCase(Range("B5").Value) = 1
    Rows("58:59").Hidden = LCase(Range("B6").Value) = "no"
ExitPoint:
    Application.EnableEvents = True
End Sub

It runs the first part of the code whenever the two "IF" statements are satisfied (Note, I added a "not" in the first).
Then it runs the second code every time the worksheet changes.

Note: I have not tested the code.


Regards,