Hide Shape unless cell holds specific text value

    I have a spreadsheet which holds a shape “Rounded Rectangle 4”


    Another cell (I34:L36) holds an IF formula which outputs either “Pass” “Fail” or remains blank


    I would like to make this shape only appear when the word pass appears in the cell as a result of the formula


    I am not very advanced with VBA so please make it as simple as possible. I have tried using multiple guides on google on how to achieve this but I cannot make it work


    Thanks in advance

    Hi and Welcome to the Forum :)


    You can add following Event macro to the Sheet Module


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address <> "$I$34" Then Exit Sub
    
    If UCase(Target.Value) = "PASS" Then
        ActiveSheet.Shapes("Rounded Rectangle 4").Visible = True
    Else
        ActiveSheet.Shapes("Rounded Rectangle 4").Visible = False
    End If
End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks 1

    Hey Carim! Thank you for your speedy response!


    I have put in your code to Module 1 of my VBA but I’m afraid it isn’t working


    I believe the issue is that my sheet holds other buttons which change other cell values


    I then have a long IF formula in cell I34 which either outputs “Pass” “Fail” or “” which is determined by the other cells which changed based on my macro buttons


    I hope this makes sense and I’m hoping there is an easy fix?

    The code must be in the worksheet code module for the sheet with the cell whose value you are monitoring. It will not work in a normal module.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Hi,


    As indicated above, an Event macro needs to be stored in the Sheet1 module ... and not in a standard VBA module ...


    Point your cursor on the Tab name ... right-click to open sub-menu and select the 5th choice View Code

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Yes I have tried this, I have entered the code into “Sheet 1” but am unable to gain the intended result


    Do my other macros also need to be in this sheet instead of in modules?

    No, they don't.


    I just noticed you said that I34 contains a formula; a Worksheet_Change event will not be triggered by a formula recalculating. You would either need to monitor the input cells for the formula, or use a Worksheet_Calculate event instead, which might look like this:


    Code
    Private Sub Worksheet_Calculate()
    
   ActiveSheet.Shapes("Rounded Rectangle 4").Visible = UCase(Range("I34").Value) = "PASS"
End Sub

    though that would be triggered whenever your sheet calculates, not just when the formula changes. That may or may not be an issue.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Aha! I think we are making progress here - using your code Worksheet_Calculate, the shape disappears as soon as a button is clicked (the other macros that change I34) but I am afraid it doesn’t reappear when I34 says Pass

    What is the actual formula in I34?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Just noticed topic has evolved ...


    When you mention "the other macros that change cell I34" ...


    Why not incorporating into these macros, the instructions related to your shape ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    So basically this spreadsheet is a checklist


    I have 5 questions which can either be a “Pass” or “Fail”. These cells are changed to pass or fail by macro buttons. These cells are G35:G43 in every other cell


    The cell in question I34 holds an if equation to verify if overall the case is a pass or fail:

    =IF(COUNTIF(G35:G43,”Fail”)>=2,”Fail”,IF(COUNTIF(G35:G43,”Pass”)>=2,”Pass”,””))


    I hope this makes sense - there probably is an easier way to go about this but as I said, I’m not 100% proficient at VBA

    Most probably, issue can be fixed if dealt with beforehand ...


    Better than bombarding you with questions about your file's structure, why don't you simply attach a sample file ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Works fine for me after updating to refer to the correct name:


    Code
    Private Sub Worksheet_Calculate()
   ActiveSheet.Shapes("Rounded Rectangle 13").Visible = UCase(Range("I34").Value) = "PASS"
End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Glad to hear you have managed to solve your problem.


    While performing several tests, your initial shape : Rounded Rectangle 4 has gradually evolved into Rounded Rectangle 13 .... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hey again :)


    I have now been asked to make this shape flash when it appears (to make it more obvious to click it). Is there any easy way to go about this?


    My code for the shape is:


    Private Sub Worksheet_Calculate()

    ActiveSheet.Shapes("Rounded Rectangle 13").Visible = UCase(Range("I34").Value) = "PASS"

    End Sub

    I'm afraid not - I can't stand flashing things on worksheets.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login