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]

I think that this has fixed it, you only need one of the procedures below, my prefence would be the first one but I've included the second one as it's close to your original.

Both appear to work ok, but I've only done a couple of quick tests.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim strPart()
    Dim shName()
If Not Intersect(Target, Range("E6:H70")) Is Nothing Then
    shName = Array("Sheet1", "Bid Results", "Sheet3", "Sheet4")
    strPart = Array("cbdn", "cnm", "efd", "etw", "ecs", "eog", "can", "cak", "cah", "cpi", "cpf", "drh", "dsn", "dsh", "emh", "emk", _
                    "eman", "emah", "emad", "ffn", "ffh", "ffd", "ebpn", "ebpd", "emrn", "emrd", "fmsn", "faun", "fauh", "fmh", "floh", _
                    "flon", "hah", "han", "ilsn", "ihrn", "ilmh", "mdh", "negn", "oan", "pash", "pasn", "pssh", "pssn", "projn", "projd", _
                    "rrah", "rran", "rwih", "rwfh", "rwfk", "rhh", "rhhm", "rhdc", "rhdp", "sph", "spd", "sksn", "stn", "ein", "wqh", _
                    "wqn", "wdsh", "wdsn")
    Sheets(shName(Target.Column - 5)).Range(strPart(Target.Row + 6) & "_" & Target.Column - 4).EntireRow.Hidden = (Target.Value = "")
End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim strPart()
If Not Intersect(Target, Range("E6:H70")) Is Nothing Then
    strPart = Array("cbdn", "cnm", "efd", "etw", "ecs", "eog", "can", "cak", "cah", "cpi", "cpf", "drh", "dsn", "dsh", "emh", "emk", _
                    "eman", "emah", "emad", "ffn", "ffh", "ffd", "ebpn", "ebpd", "emrn", "emrd", "fmsn", "faun", "fauh", "fmh", "floh", _
                    "flon", "hah", "han", "ilsn", "ihrn", "ilmh", "mdh", "negn", "oan", "pash", "pasn", "pssh", "pssn", "projn", "projd", _
                    "rrah", "rran", "rwih", "rwfh", "rwfk", "rhh", "rhhm", "rhdc", "rhdp", "sph", "spd", "sksn", "stn", "ein", "wqh", _
                    "wqn", "wdsh", "wdsn")
    Application.Goto strPart(Target.Row + 6) & "_" & Target.Column - 4
    Selection.EntireRow.Hidden = (Target.Value = "")
    Application.Goto "T" & Target.Column - 4 & "_" & strPart(Target.Row + 6)
End If
    Application.ScreenUpdating = True
End Sub

 

[jtatt]

Thank you
I applied your first solution above

this did unhide a range, i tried it for cbdn which unhid dsn. dsn is 12 rows below cbdn on the E6:H70 grid

also ihrn unhid rrah also 12 rows down inthe grid

rwfk unhid wdsn, 13 rows down

it successfully unhid and rehid on the 4 sheets

from rhh (which is row 58 on the grid)onwards it errors with out of range

 

[jtatt]

i had a look through strPart = Arrayand found one typo cmn entered as cnm also rwik was not included, i added rwik and corrected cmn

now all rows of the grid are hiding and unhiding a range 12 rows below the required unhide range

from row 59 of the grid, rhhm, it errors with out of range

 

[jasonb75]

Sorry, I forgot to say that part of the first one will need editing. The line shName = Array() needs to be filled with the names of your sheet. The names that are in the code at the moment are the sheets in the workbook that I used for testing.

I have just noticed an error that I missed earlier, (Target.Row + 6) should be (Target.Row - 6) this applies to both methods.

edit:- ignore below this line, looks like you fixed that while I was typing.

Also, you will need to check the strPart array to make sure that the names are all present and in the correct order, the first one, "cbdn" should relate to row 6, the last one, "wdsn" to row 70. I thought that I copied them all correctly from your code, but having just done a quick count, I think that one could be missing somewhere in the middle.

Once you have made the adjustments above, you should be able to find the position of the missing name by testing a few rows. If you clear a cell in the grid and the correct rows are hidden, then it means the missing name is below that in the grid, if the wrong rows are hidden then the missing name will be above.

I'll have a quick look through the code again to see if I can see the missing one, but with 800 or so lines

 

[jtatt]

it was rwik which was missing
yes thank you I used the sheet names

 

[jasonb75]

I presume that when you say that you 'added' rwik, that you put it into the middle of the array so that it occupied the correct position in the grid.

Something else that just came to mind, how do the names "T1_xxx" relate to the Target cell?

If, for example, "T1_cbdn" is the name of E6, etc then we should be able to capture the target name and use that in place of the array.

 

[jtatt]

it is working thanks very much
I'm inspired to learn arrays & improve my skills, including writing shorter codes
thanks again for the wonderful help

 

[jtatt]

yes you are correct

The number entered into T1_cbdn is the target for the 1st quarter for cbdn and yes it is in E6, T2_cbdn is F6
T1_cmn E7 and so on

are there benefits of what you suggest using in place of array ?

 

[jasonb75]

are there benefits of what you suggest using in place of array ?

As long as they follow the same naming convention, you could add more names if needed without having to edit the code as well.

If the four sheets are named similarly, then it could probably be done there as well. (I would need the sheet names to set up the parameters).

 

[jtatt]

I will be the one editing the sheet for my colleague I imagine. Not sure if there will be changes, but good idea to provide for them


so the grid is on sheet "CONTROL PAGE", column E is all T1, col F is T2, col G is T3 and col H is T4

the T1 results are unhidden on sheet "Q1ReportingForm"
T2 on "Q2ReportingForm"
T3 on "Q3ReportingForm"
T4 on "Q4ReportingForm"