merge two Private Sub Worksheet_Change(ByVal Target As Range)

[mark9988]

Hello - I have two Worksheet_Change events and no luck merging these two. Any assistance would be greatly appreciated!




Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$E$30") Then
If Target.Value = "1" Then
Rows("38:38").EntireRow.Hidden = False
Rows("39:47").EntireRow.Hidden = True

ElseIf Target.Text = "2" Then
Rows("38:39").EntireRow.Hidden = False
Rows("40:47").EntireRow.Hidden = True

ElseIf Target.Text = "3" Then
Rows("38:40").EntireRow.Hidden = False
Rows("41:47").EntireRow.Hidden = True


End If
End If
End Sub


AND


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ISSUE
If Target(1).Address = Me.Range("AJ85").Address Then
Application.EnableEvents = False
ThisWorkbook.Worksheets("Projection Summary").Range("AB17").Value = Target.Value
ThisWorkbook.Worksheets("Projection Summary").Range("AB70").Value = Target.Value
ThisWorkbook.Worksheets("Projection Summary").Range("AB133").Value = Target.Value
ThisWorkbook.Worksheets("Projection Comparison").Range("H7").Value = Target.Value

End If
Application.EnableEvents = True
Exit Sub

ISSUE:
Application.EnableEvents = True
VBA.MsgBox "Error " & Err.Number
End If
End Sub



Thanks!!!

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit sub if multiple cells updated at once (i.e. whole row deleted)
    If Target.CountLarge > 1 Then Exit Sub

'***FIRST BLOCK***
    If Target.Address = "$E$30" Then
        Select Case Target.Value
            Case "1"
                Rows("38:38").EntireRow.Hidden = False
                Rows("39:47").EntireRow.Hidden = True
            Case "2"
                Rows("38:39").EntireRow.Hidden = False
                Rows("40:47").EntireRow.Hidden = True
            Case "3"
                Rows("38:40").EntireRow.Hidden = False
                Rows("41:47").EntireRow.Hidden = True
        End Select
    End If

'***SECOND BLOCK***
    On Error GoTo ERR_ISSUE

    If Target.Address = "$AJ$85" Then
        Application.EnableEvents = False
        ThisWorkbook.Worksheets("Projection Summary").Range("AB17").Value = Target.Value
        ThisWorkbook.Worksheets("Projection Summary").Range("AB70").Value = Target.Value
        ThisWorkbook.Worksheets("Projection Summary").Range("AB133").Value = Target.Value
        ThisWorkbook.Worksheets("Projection Comparison").Range("H7").Value = Target.Value
        Application.EnableEvents = True
    End If

    Exit Sub

ERR_ISSUE:
    Application.EnableEvents = True
    MsgBox "Error " & Err.Number

End Sub

Note that I made some minor changes, such as:
- checking to see only one cell was updated (otherwise, deleting rows could make the whole thing choke and cause errors)
- Used a "Case" statement instead of multiple "ElseIF" statements (I find this much cleaner). See: MS Excel: How to use the CASE Statement (VBA)

 

[mark9988]

Excellent - Thank you!

 

[Joe4]

You are welcome.

Note when marking the solution, you want to mark the actual post containing the solution (not the post acknowledging that a solution has been found).
That allows users to see the original question and solution quicly in a single glance.

I have updated it for you.

 

[dmt32]

Hello - I have two Worksheet_Change events and no luck merging these two. Any assistance would be greatly appreciated!
Thanks!!!

Hi
Just seen this post & appreciate have a solution but another approach you could consider maybe

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws          As Worksheet
    
    If Target.CountLarge > 1 Then Exit Sub
    
    On Error GoTo ISSUE
    If Not Intersect(Target, Range("$E$30", "AJ85")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Address
        
            Case "$E$30"
            
                If Val(Target.Value) > 0 And Val(Target.Value) < 4 Then
                    Me.Rows(Choose(Target.Value, "38:38", "38:39", "38:40")).EntireRow.Hidden = False
                    Me.Rows(Choose(Target.Value, "39:47", "40:47", "41:47")).EntireRow.Hidden = True
                End If
                
            Case Else
            
                Set ws = ThisWorkbook.Worksheets("Projection Summary")
                ws.Range("H7,AB17,AB70,AB133").Value = Target.Value

        End Select
    End If
     
ISSUE:
    Application.EnableEvents = True
    If Err <> 0 Then VBA.MsgBox (Error(Err)), 48, "Error"
End Sub

Idea not fully tested but hopefully, does what you want.

Dave