the macro does not work with the formula in the cell

[Beleric3380]

Hello!
I have a macro that expands or collapses rows depending on the value. If we fill in the values ourselves or from the list, then everything works.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 2 Then

On Error Resume Next

If Target = "Yes" Then

Rows(Target.Row + 1).ShowDetail = Target = "Yes"

ElseIf Target = "No" Then

Rows(Target.Row + 1).ShowDetail = Target = "Yes"

End If

On Error GoTo 0

End If

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

That is because the Worksheet_Change event is not triggered by a formula calculation.

What is the actual formula in column 2? (Say for row 3)

 

[Beleric3380]

Hello! Adding a picture. B3 Sheet1 = B10 Sheet2 and it's not working. B9 sheet1 is a list and all working good

 

[Peter_SSs]

Thanks. The question then becomes: Is cell B10 in Sheet2 also a formula?
If it is not a formula, try the Worksheet_Change event in the Sheet2 module.
This is only set for one set of rows in Sheet1 but is testing to see if it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$10" Then
    Sheets("Sheet1").Rows(4).ShowDetail = Target.Value = "Yes"
  End If
End Sub

 

[Beleric3380]

Thanks. The question then becomes: Is cell B10 in Sheet2 also a formula?
If it is not a formula, try the Worksheet_Change event in the Sheet2 module.
This is only set for one set of rows in Sheet1 but is testing to see if it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$10" Then
    Sheets("Sheet1").Rows(4).ShowDetail = Target.Value = "Yes"
  End If
End Sub

B10 — это не формула. все работает. Спасибо. как сделать чтобы разворачивание было на несколько слов (Да, Да_1, Да_2...) пробовал так

[КОД=vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Then
Sheets("FARA OPP").Rows(50).ShowDetail = Target.Value = "Yes"
ElseIf Target.Address = "$B$10" Then
Sheets("FARA OPP").Rows(50).ShowDetail = Target.Value = "Yes_1"
End If
End Sub
[/КОД]

 

[Peter_SSs]

Please post in English only in this forum.

 

[Beleric3380]

Please post in English only in this forum.

B10 is not a formula. everything works. Thanks. how to make the unfolding be a few words (Yes, Yes_1, Yes_2...) I tried it like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Then
Sheets("FARA OPP").Rows(50).ShowDetail = Target.Value = "Yes"
ElseIf Target.Address = "$B$10" Then
Sheets("FARA OPP").Rows(50).ShowDetail = Target.Value = "Yes_1"
End If
End Sub

 

[Peter_SSs]

how to make the unfolding be a few words (Yes, Yes_1, Yes_2...)

Try

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$10" Then
    Select Case Target.Value
      Case "Yes", "Yes_1", "Yes_2"
        Sheets("FARA OPP").Rows(50).ShowDetail = True
      Case Else
        Sheets("FARA OPP").Rows(50).ShowDetail = False
    End Select
  End If
End Sub

 

[Peter_SSs]

Another way would be

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$10" Then Sheets("FARA OPP").Rows(50).ShowDetail = InStr(1, "|Yes|Yes_1|Yes_2|", "|" & Target.Value & "|")
End Sub

 

[Beleric3380]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$10" Then
    Select Case Target.Value
      Case "Yes", "Yes_1", "Yes_2"
        Sheets("FARA OPP").Rows(50).ShowDetail = True
      Case Else
        Sheets("FARA OPP").Rows(50).ShowDetail = False
    End Select
  End If
End Sub

Thank you! It's working.