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

 

[jtatt]

I see why you are banging your head on table

I wanted to create change event where
If any cells E6:H70 are greater than zero a range is unhidden in one of four other worksheets
If those cells with value greater than zero are then cleared the rows are hidden again

It worked for some time but then became too large which was when, from some research I tried to do, I thought I could have two events for the one sheet. One event for cells E6:H37 and the second for E38:H70. That was when I attempted to specify the target for each of the two events

 

[My Aswer Is This]

Ok I'm learning more.

You said:

If any cells E6:H70 are greater than zero a range is unhidden in one of four other worksheets

So give me specifics Like what is the name of the range you want hidden if any value in Range("E6:H70") changes from Greater then 0

We always need exact specifics.
​

 

[jtatt]

okey dokey
I have been trialling reducing my code whilst messaging , anyway..... to answer your question

there are four separate sheets of named ranges which are to be hidden or unhidden from the sheet with change event on it


column E , in E6:E70 if they greater than zero will unhide names in the 1st other sheet
if E6 is greater than zero, range cbdn_1 is unhidden on 1st other sheet


column F, F6:F70 will unhide names in the 2nd other sheet
if F6 is greater than zero, range cbdn_2 is unhidden on the 2nd other sheet

column G , G6:G70 unhides ranges in the 3rd other sheet
if G6 is greater than zero, range cbdn_3 is unhidden on the 3rd other sheet

column H, H6:H70 unhides ranges in the 4th other sheet
if H6 is greater than zero, range cbdn_4 is unhidden on the 4th other sheet

 

[My Aswer Is This]

If what your last post is what you wanted.

You could use something like this:

This script assumes the range name is "Home" which is on Sheet(2)

Modify to your needs

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/1/2019  5:58:47 AM  EDT
If Not Application.Intersect(Target, Range("E6:H37")) Is Nothing Then
Select Case Target.Value
    Case Is > 0
        Sheets(2).Range("Home").EntireRow.Hidden = True
Case ""
    Sheets(2).Range("Home").EntireRow.Hidden = False
End Select

End If
End Sub

 

[jtatt]

so significantly reduce the code volume so it can be done in one change event

thanks very much, that is very helpful

 

[My Aswer Is This]

It is hard to help when you say:

1st other sheet

Is this really a sheet name?

I need a sheet name like:

"Alpha"

Or

"Bravo"


​

 

[jasonb75]

A compressed version of your entire code (think I have it right, might be a couple of tweaks needed).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strPart() As Variant
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")
    Range(strPart(Target.Row + 6) & "_" & Target.Column - 4).EntireRow.Hidden = (Target.Value = "")
    Application.Goto "T" & Target.Column - 4 & "_" & strPart(Target.Row + 6)
End If
End Sub

 

[jtatt]

thank you
this gave run-time error 1004
Method range of object_worksheet failed

 

[jasonb75]

Which line was the error on?

When you debug, move the cursor over strPart in the error line, does the correct name show up in the tooltip?

 

[jtatt]

error on this line
Range(strPart(Target.Row + 6) & "_" & Target.Column - 4).EntireRow.Hidden = (Target.Value = "")

Range = <Method 'Range of object'_'Worksheet'failed>

it pops up strPart(Target.Row+6)="dsn"

dsn is one of manyranges which may be hidden or unhidden. on the 1st,2nd,3rd,4th sheets each has 65 ranges on them