Visual Basic Coding

I have some Visual Basic code I am using in Excel. The intent is for certain rows in a spreadsheet to be hidden when an "X" is entered in a cell, and for those rows (or at least some of those rows) to unhide when the X is removed. The code works fine if I manually type an X into the cell. But, if I use a formula in the cell to generate the X on certain conditions, then it won't work. I need help modifying the code so that I can use a formula to populate the cell with the X. Here, the cell is named TwoA2, and the range that is hidden is TwoATwo. Here is the code, with the relevant part in bold:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("TwoA2").Address Then
   
        Dim cell As Range

   
   ' If x on the line, then hide entire section
        If [TwoA2] = "X" Then
   
            [TwoATwo].Select
            Selection.EntireRow.Hidden = True
            [TwoA2].Select
     
        End If

   
    ' If no x on the line, then unhide instructions and relevant rows
       
        If [TwoA2] <> "X" Then
       
          ' First, unhide entire section
            [TwoATwo].Select
            Selection.EntireRow.Hidden = False
         
          ' Now hide empty rows
            For Each cell In [TwoA2Check]
            If IsEmpty(cell) = True Then
            cell.EntireRow.Hidden = True
            Else
            cell.EntireRow.Hidden = False
            End If
            Next
         
        End If
       
    ' Return cursor to active cell on ending
     
        [TwoA2].Select

       
    End If


   
End Sub
carlosabAsked:
Who is Participating?
 
Ryan ChongCommented:
I need help modifying the code so that I can use a formula to populate the cell with the X.

if now cell: TwoA2 is formula based, try remark the line:

If Target.Address = Range("TwoA2").Address Then

Open in new window

and its:

End If

Open in new window


reason being now Target.Address will not always be as Range("TwoA2").Address, since you're not changing TwoA2 directly.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You should use Worksheet_Calculate Event if the cell content is being changed by a formula.
The Change Event will only work if the cell content is being changed explicitly.

Remove your existing change event code and place the following code on Sheet Module.
Private Sub Worksheet_Calculate()
Dim cell As Range
Application.EnableEvents = False
Application.ScreenUpdating = False
Range("TwoA2").Rows.Hidden = False
For Each cell In Range("TwoA2")
    cell.Select
    If LCase(cell) = "x" Then
        Rows(cell.Row).Hidden = True
    Else
        Rows(cell.Row).Hidden = False
    End If
Next cell
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
carlosabAuthor Commented:
This worked thank you.

I posted a different question related to the same code.  https://www.experts-exchange.com/questions/29083916/Visual-Basic-Coding-Hide-rows.html#a42468401. You thought that it was a duplicate question, but it isn't. Would you mind taking a look at that question too?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.