Multiple worksheet change events in one sheet

[vekdas]

Hi

Can anyone help - I am trying to create two worksheet changes which need to happen as a result of a change in a cell. There are two sets of code below which I believe I need to try and combine but I am not sure how??

The code for the first change is: (this works fine on its own - it hides rows wherever the word 'delete' is in a specific cell within that row (within a range called "Trusts2" , otherwise it does not hide the row.
This code has to run through a number of rows to decide whether to hide or show the row.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRg As Range

Application.ScreenUpdating = False

For Each xRg In Range("Trusts2")
If xRg.Value = "Delete" Then
xRg.EntireRow.Hidden = True

Else

xRg.EntireRow.Hidden = False

End If

Next xRg

Application.ScreenUpdating = True

End Sub


The second code I want to add to this should update all cells in a known range to "Yes" on the same sheet. If I was just writing a simple macro for it, it would look like this:

Sub Reset_Comparison_Trusts()

Sheet15.Range("YesorNo2").Value = "Yes"

End Sub

Many thanks for any help in showing how best to combine these!

 

[HaHoBe]

Hi vekdas,

what about

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range
Dim blnRun As Boolean

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Trusts2")) Is Nothing Then
  For Each rngCell In Range("Trusts2")
    If rngCell.Value = "Delete" Then
      rngCell.EntireRow.Hidden = (rngCell.Value = "Delete")
    End If
  Next rngCell
ElseIf Not Intersect(Target, Range("YesorNo2")) Is Nothing Then
  Application.EnableEvents = False
  Range("YesorNo2").Value = "Yes"
  Application.EnableEvents = True
End If

Application.ScreenUpdating = True

End Sub

Ciao,
Holger

 

[vekdas]

Hi vekdas,

what about

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range
Dim blnRun As Boolean

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Trusts2")) Is Nothing Then
  For Each rngCell In Range("Trusts2")
    If rngCell.Value = "Delete" Then
      rngCell.EntireRow.Hidden = (rngCell.Value = "Delete")
    End If
  Next rngCell
ElseIf Not Intersect(Target, Range("YesorNo2")) Is Nothing Then
  Application.EnableEvents = False
  Range("YesorNo2").Value = "Yes"
  Application.EnableEvents = True
End If

Application.ScreenUpdating = True

End Sub

Ciao,
Holger

Hi Holger

Many thanks for your code.

This changes the cells in 'YesorNo2' to 'Yes' whenever a change is made anywhere in the sheet. I actually only want it to update if cell C9 (named as "ChosenICS2") is altered.

Also, the code no longer hides any rows which have the word 'delete' in the same row in the "Trusts2" list.

The code is being used in a complex model I can't share so I will try to create a simple model for explanation.

Best Wishes

Kev

 

[vekdas]

Hi Holger

Many thanks for your code.

This changes the cells in 'YesorNo2' to 'Yes' whenever a change is made anywhere in the sheet. I actually only want it to update if cell C9 (named as "ChosenICS2") is altered.

Also, the code no longer hides any rows which have the word 'delete' in the same row in the "Trusts2" list.

The code is being used in a complex model I can't share so I will try to create a simple model for explanation.

Best Wishes

Kev

PS - I do need to be able to change the cells with Yes or No manually but have them revert to "Yes" when I change a drop down menu in cell C9. With the code you sent when I change them manually to "No" they automatically change back to "Yes" straight away

 

[vekdas]

Hi

Can anyone help - I am trying to create two worksheet changes which need to happen as a result of a change in a cell. There are two sets of code below which I believe I need to try and combine but I am not sure how??

The code for the first change is: (this works fine on its own - it hides rows wherever the word 'delete' is in a specific cell within that row (within a range called "Trusts2" , otherwise it does not hide the row.
This code has to run through a number of rows to decide whether to hide or show the row.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRg As Range

Application.ScreenUpdating = False

For Each xRg In Range("Trusts2")
If xRg.Value = "Delete" Then
xRg.EntireRow.Hidden = True

Else

xRg.EntireRow.Hidden = False

End If

Next xRg

Application.ScreenUpdating = True

End Sub


The second code I want to add to this should update all cells in a known range to "Yes" on the same sheet. If I was just writing a simple macro for it, it would look like this:

Sub Reset_Comparison_Trusts()

Sheet15.Range("YesorNo2").Value = "Yes"

End Sub

Many thanks for any help in showing how best to combine these!

 

[vekdas]

I have created a simple example model but I can't see how to upload it here?!

 

[HaHoBe]

Hi Kev,

altered code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range
Dim rngArea As Range

If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Trusts2")) Is Nothing Then
  For Each rngArea In Intersect(ActiveSheet.UsedRange, Range("Trusts2").EntireRow).Rows
    For Each rngCell In rngArea.Cells
      If LCase(rngCell.Value) = "delete" Then
        rngCell.EntireRow.Hidden = True
        Exit For
      Else
        rngCell.EntireRow.Hidden = False
      End If
    Next rngCell
  Next rngArea
ElseIf Not Intersect(Target, Range("C9")) Is Nothing Then
  If LCase(Range("C9").Value) = "do it" Then
    Application.EnableEvents = False
    Range("YesorNo2").Value = "Yes"
    Application.EnableEvents = True
  End If
End If

Application.ScreenUpdating = True

End Sub

I changed the values to compare to be lower case and added "do it" to trigger the code for C9.

You cannot upload anything here, you would need a free service like DropBox, OneDrive, Google Drive for upload and post a link here.

Holger

 

[vekdas]

Hi Kev,

altered code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range
Dim rngArea As Range

If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Trusts2")) Is Nothing Then
  For Each rngArea In Intersect(ActiveSheet.UsedRange, Range("Trusts2").EntireRow).Rows
    For Each rngCell In rngArea.Cells
      If LCase(rngCell.Value) = "delete" Then
        rngCell.EntireRow.Hidden = True
        Exit For
      Else
        rngCell.EntireRow.Hidden = False
      End If
    Next rngCell
  Next rngArea
ElseIf Not Intersect(Target, Range("C9")) Is Nothing Then
  If LCase(Range("C9").Value) = "do it" Then
    Application.EnableEvents = False
    Range("YesorNo2").Value = "Yes"
    Application.EnableEvents = True
  End If
End If

Application.ScreenUpdating = True

End Sub

I changed the values to compare to be lower case and added "do it" to trigger the code for C9.

You cannot upload anything here, you would need a free service like DropBox, OneDrive, Google Drive for upload and post a link here.

Holger

 

[vekdas]

Hi Holger

Unfortunately this didn't work to change the cells back to Yes or hide the lines.

I have added a file with the query to a folder in my Google Drive if you have any time to take a look

Many thanks again

Macro Query.xlsm

drive.google.com

The cell that will drive the event change is C3 in this version (the yellow cell)

 

[HaHoBe]

Hi Kev,

from looking at your workbook I think it will be good enough to just monitor changes to C3 to trigger the code: change the contents in Range YesorNo2 to Yes, make all rows for the range Trusts2 and loop through Range Trusts2 to hide all rows where Delete is found. I still try to find out what you mean by

I need to be able to change the pink cells above to "No" if I don’t wnt to include a row in the analysis in the main model

The code so far as described above:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range

'allow only one cell to be changed to start the processing
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Not Intersect(Target, Range("C3")) Is Nothing Then
  Application.EnableEvents = False
  Range("YesorNo2").Value = "Yes"
  Range("Trusts2").EntireRow.Hidden = False
  Application.EnableEvents = True
  For Each rngCell In Range("Trusts2")
    rngCell.EntireRow.Hidden = (rngCell.Value = "Delete")
  Next rngCell
End If

Application.ScreenUpdating = True

End Sub

As you can see from my profile I can run the code but will get just errors as I'm still using 2019. So I kept the data by using a different cell to trigger.

Ciao,
Holger