Combining multiple Worksheet Change Events

Milos

Board Regular
Joined
Aug 28, 2016
Messages
101
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:

Code:
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
Code:
  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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi,

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

Code:
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,
 

Forum statistics

Threads
1,077,642
Messages
5,335,416
Members
399,014
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top