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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.