Worksheet_Change(ByVal... Not Triggering

[jvanbonn]

Any suggestions why the following code is not triggered when I change the value of cell A1 to TRUE or FALSE? The code is in the active worksheet. All suggestions to make the code more elegant are welcome too.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$1" Then
  Exit Sub
End If

Application.enableevents = False

Dim h As Long, i As Long, j As Long, k As Long
Dim Rng As Range

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'  h is the row index of the selected range (looping)
'  i is the row index of first non-header row of selected range
'  j is the row index of the last row of the selected range
'  k is the kth member of the Range Rng
'
'  Rng is the union of rows in the selected range where
'       column A has the value "TRUE" <= to be hidden
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If Target.Address = "$A$1" And Target.Value = True Then

  With ActiveSheet.Cells(4, 2).CurrentRegion
  i = 5 '.Item(2, 1).Row                        'FIRST ROW OF DATA
  j = .Item(.Item.Count).Row                 'LAST ROW OF DATA
  End With

  k = 0
    
    For h = i To j Step 1
    
      If ActiveSheet.Cells(h, 1) = True Then
      
        k = k + 1
        
          If k = 1 Then
          
            Set Rng = ActiveSheet.Cells(h, 1)
            Debug.Print Rng.Address
            
          Else
          
            Set Rng = Union(Rng, ActiveSheet.Cells(h, 1))
            Debug.Print Rng.Address
            
          End If
          
      End If
      
    Next h
    
  Rng.EntireRow.Hidden = True

ElseIf Target.Address = "$A$1" And Target.Value = False Then
 
   ActiveSheet.Cells.EntireRow.Hidden = False

End If

Application.enableevents = True

End Sub

Thanks!

 

[Norie]

How are you changing A1?

 

[jvanbonn]

With a checkbox.

 

[Norie]

That might not be triggering the event at all.

Yep, just checked.

Clicking a checkbox won't trigger the change event.

Not sure what your code is meant to do but you could try using the checkbox's click event to run it.

You wouldn't be able to use Target but since you know what cell will be changed you don't really need it, so just replace it with Range("A1").

PS The code for finding the last row doesn't seem to work.

If you want to find the last row in a range then take a look at it's Rows (and Row) property.

Something like this perhaps, though I'm not 100% sure it'll work - the headers starting in row 4 seem to be making things a lot harder.

   With ActiveSheet.Cells(4, 2).CurrentRegion
            i = 5    '.Item(2, 1).Row                        'FIRST ROW OF DATA
            j = .Row + .Rows.Count - 1           'LAST ROW OF DATA
        End With

 

[jvanbonn]

Thanks again!

Very strange, because it used to work. If I manually type TRUE or FALSE in A1, the checkbox changes to checked or unchecked, but the Worksheet_Change(ByVal... still does not trigger.

I'll try your suggestion to trigger it with the checkbox's click event.

PS the last time the code worked I used:

  With ActiveSheet.Cells(4, 2).CurrentRegion
  i = 5 '.Item(2, 1).Row                        'FIRST ROW OF DATA
  j = .Item(.Rows.Count, .Columns.Count).Row   'LAST ROW OF DATA
  'j = .Item(.Item.Count).Row                    'LAST ROW OF DATA
  End With

to determine the last row. I was trying something new, but never could get the code to trigger again.


Another thought I had...

Could the Application.enableevents = False be permanently set? Also, if I try to type ".EnableEvents" with upper case Es, the VB editor changes it to lowercase, which I find really weird.

 

[Norie]

Well I think the checkbox thing can sometimes be affected by what version of Excel you are using.

It's probably 'safest' to use the click event if you want the code to run when that changes from checked to unchecked or vice versa.

When I tried the original code the part for finding the range failed on .Items.

As for the enable events thing, it is possible events have been accidentally disabled.

You can easily enable them by entering Applicatiion.EnableEvents = TRUE in the Immediate window (CTRL+G).

Perhaps more intriguing is VBE changing EnableEvents to lowercase.

The usual reason for that is because at some point 'EnableEvents' has been used for the name of a variable, a sub, a function etc.

Could that have happened?

 

[jvanbonn]

I didn't know about typing "Application.EnableEvents = TRUE" in the Immediate window. I learned something new.

And yes, one of my bright ideas for the day was to create a subroutine to set EnableEvents = TRUE. Then I immediately forgot that I did that.

Sub enableevents()
Application.enableevents = True
'MsgBox Application.enableevents
End Sub

However, now that I have deleted that subroutine, I cannot eliminate the lower case "e"s.

It's a good thing I have a sense of humor and can laugh at myself.

Thanks for all you help and insights!