Set range for Multiple change events on one sheet each referring to a different range of cells

[jtatt]

1 change event worked on the worksheet until it got too large. I split the 1 event into two events. One titled “Private Sub Worksheet_Change1(ByVal Target As Range)” which I want to work for changes in range E38:H70
The 2^nd event “Private Sub Worksheet_Change(ByVal Target As Range)” is for changes in range E6:H37

The result was object variable or block variable not set errors. To resolve this I tried to set targets by either
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E6:H37")) Is Nothing Then
Followed by code which worked prior to splitting into 2
AND
I also tried Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row >= 6 And Target.Row <= 37 Then
Followed by code which worked prior to splitting into 2

I have also tried to set target with If Not Application.Intersect(Target, Range("E6:H37")) Is Nothing Then

As a result now when changes are made E6:H37 the change event works. Changes within E38:H70 do not work, there are no error messages. Nothing happens

This is the start of each event:
The sub which is working:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E6:H37")) Is Nothing Then


'Part1 cbdn
If Target.Address = "$E$6" Then
Select Case Target.Value
Case Is > 0
Application.Goto Reference:="cbdn_1"
Selection.EntireRow.Hidden = False
Application.Goto Reference:="T1_cbdn"
Case Is = ""
Application.Goto Reference:="cbdn_1"
Selection.EntireRow.Hidden = True
Application.Goto Reference:="T1_cbdn"
End Select
End If
Which continues on for many rows ending with
End If
End Sub


The event which is not working:
Private Sub Worksheet_Change1(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E38:H70")) Is Nothing Then

'Part1 hah
If Target.Address = "$E$38" Then
Select Case Target.Value
Case Is > 0
Application.Goto Reference:="hah_1"
Selection.EntireRow.Hidden = False
Application.Goto Reference:="T1_hah"
Case Is = ""
Application.Goto Reference:="hah_1"
Selection.EntireRow.Hidden = True
Application.Goto Reference:="T1_hah"
End Select
End If

and so on ending with End If and End Sub

Any assistance would be much appreciated, thank you

 

[jasonb75]

This is what I had in mind, I've not tested it (easier to test with the proper sheet).

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Name.Name Like "T#_?*" Then Sheets("Q" & Mid(Target.Name.Name, 2, 1) & "ReportingForm").Range(Mid(Target.Name.Name & "_" & Mid(Target.Name.Name, 2, 255), 4, Len(Target.Name.Name) - 1)).EntireRow.Hidden = (Target.Value = "")
End Sub

This method would result in an error every time you make a change outside of the grid, to compensate for that I've used the brute force method of error handling.
The only downside to this is any incorrectly named named ranges will be ignored rather than going into debug mode.

The earlier method that you now have working will go into debug if there are any name errors, but you would need to manually change the code if you add new ranges later. This may be the better option if you are unlikely to add more named ranges.

If you were going to be adding more names on a regular basis, then I would have said that the new method would be better (perhaps with some changes to the error trap to distinguish between changes out of the grid and mismatched names in the grid).

Hopefully this gives you enough info to make a decision.

 

[jtatt]

thanks again. I'll have a play with it, a think and chat to colleague about how it might best work
thanks for all your efforts, I have learned a lot, and now - my model works

 

[jtatt]

Thank you for your assistance with change events and how I can reduce the size of my codes