Show/Hide a textbox based on the value of a cell

[Matteo85]

I have a table in my worksheet that populates with a spill of the main table if any project in the main table is unfinished. I need to show a textbox if there are no unfinished projects to overlay the area of the spill array and hide it if there is any value in the spill array.

How could this be done? I've tried the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 14 And Target.Column = 2 Then ' The spill array's upper left cell is B14
        If Target.Value = "" Then
            ActiveSheet.Shapes("txtbx_NoOpenProjects").Visible = True
        Else
            ActiveSheet.Shapes("txtbx_NoOpenProjects").Visible = False
        End If
    End If
End Sub

but I can't get the textbx_NoOpenProjects to appear/disappear. The spill array is working properly and is based on the main table in another worksheet.

Any help?

 

[6StringJazzer]

I am guessing that B14 has a formula. If that is the case then your If condition will never be True because Target will never be B14. The Worksheet_Change condition is not triggered by a change to the result of formula, only when the actual content of a cell changes.

Try this instead:

Private Sub Worksheet_Calculate()

        If Range("B14").Value = "" Then
            ActiveSheet.Shapes("txtbx_NoOpenProjects").Visible = True
        Else
            ActiveSheet.Shapes("txtbx_NoOpenProjects").Visible = False
        End If

End Sub

 

[sinoyon780]

Private Sub Worksheet_Calculate()
    Dim spillRange As Range
    Set spillRange = Range("B14").CurrentRegion ' Assuming the spill array starts from B14
    
    If WorksheetFunction.CountA(spillRange) = 0 Then
        ActiveSheet.Shapes("txtbx_NoOpenProjects").Visible = True
    Else
        ActiveSheet.Shapes("txtbx_NoOpenProjects").Visible = False
    End If
End Sub

 

[Matteo85]

Neither of the solutions above seems to be working. Is it possibly due to the action taking place on another worksheet?

I tried moving the logic into another cell that checks to see if the spill array has a value and returns "0" or "1" depending. That doesn't seem to work either.

Code in A14:

'=IF(B14="","0","1")

Any ideas?

 

[Matteo85]

I got the following to work. Thanks for pointing me in the right direction.

Private Sub Worksheet_Calculate()
    Dim wk As Worksheet
    Set wk = Worksheets("Sheet1")
    If wk.Range("B14").Value = "" Then
            wk.Shapes("txtbx_NoOpenProjects").Visible = True
        Else
            wk.Shapes("txtbx_NoOpenProjects").Visible = False
    End If
End Sub

 

[Peter_SSs]

I got the following to work.

If the formula in B14 is on Sheet1, and the shape is on Sheet1 and the Worksheet_Calculate code is in the Sheet1 module, then I don't see how the post 2 code works in any way differently to the code in post 5. Can you explain the difference?

BTW these 5 lines of code ..

If wk.Range("B14").Value = "" Then
        wk.Shapes("txtbx_NoOpenProjects").Visible = True
    Else
        wk.Shapes("txtbx_NoOpenProjects").Visible = False
End If

.. could be replaced with this single line

wk.Shapes("txtbx_NoOpenProjects").Visible = wk.Range("B14").Value = ""

 

[smozgur]

I got the following to work. Thanks for pointing me in the right direction.

Private Sub Worksheet_Calculate()
    Dim wk As Worksheet
    Set wk = Worksheets("Sheet1")
    If wk.Range("B14").Value = "" Then
            wk.Shapes("txtbx_NoOpenProjects").Visible = True
        Else
            wk.Shapes("txtbx_NoOpenProjects").Visible = False
    End If
End Sub

The original question points to the active sheet, and the provided solution in post #2 has been prepared to fulfill that requirement. Therefore, post #2 is the initial correct answer of the question and I switched it as the solution post as @Peter_SSs also mentioned above.

 

[michalina]

Hi i have a very similar question and can't find a solution. My goal is to show Chart Ratio_All and hide Ratio_Months when the value in cell B1 (from the formula) is equal to 1 and revers it when its 2. Below my VBA. Everything is in Shee1

Private Sub Worksheet_Calculate()
Dim wk As Worksheet
Set wk = Worksheets("Sheet1")
If wk.Range("B1").Value = 1 Then

ActiveSheet.Shapes("Ratio_All").Visible = True
ActiveSheet.Shapes("Ratio_Months").Visible = False

Else

ActiveSheet.Shapes("Ratio_All").Visible = False
ActiveSheet.Shapes("Ratio_Months").Visible = True

End If
End With

End Sub

Any idea what i do wrong ?

 

[michalina]

Hi i have a very similar question and can't find a solution. My goal is to show Chart Ratio_All and hide Ratio_Months when the value in cell B1 (from the formula) is equal to 1 and revers it when its 2. Below my VBA. Everything is in Shee1

Private Sub Worksheet_Calculate()
Dim wk As Worksheet
Set wk = Worksheets("Sheet1")
If wk.Range("B1").Value = 1 Then

ActiveSheet.Shapes("Ratio_All").Visible = True
ActiveSheet.Shapes("Ratio_Months").Visible = False

Else

ActiveSheet.Shapes("Ratio_All").Visible = False
ActiveSheet.Shapes("Ratio_Months").Visible = True

End If
End With

End Sub

Any idea what i do wrong ?

found the solution

Private Sub Worksheet_Calculate()
Dim wk As Worksheet
Set wk = Worksheets("Sheet1")
  If wk.Range("B1").Value = 1 Then
 

  wk.Shapes("Ratio_All").Visible = True
  wk.Shapes("Ratio_Months").Visible = False
 
Else

  wk.Shapes("Ratio_All").Visible = False
  wk.Shapes("Ratio_Months").Visible = True
 
  End If

End Sub

 

[Peter_SSs]

@michalina
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags in post 9 for you this time. Compare that code to your code in post 8 where I have not added the tags.