Hide button on worksheet if there is nothing contained in cell E24

[TheJay]

I've written some code but it's not working and I am not sure why.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Cells(5, 24).Value <> "1" Then
        ActiveSheet.Shapes("Button412").Visible = True
        Else
        ActiveSheet.Shapes("Button412").Visible = False
    End If
    Application.ScreenUpdating = True
End Sub

I want to make sure the button is hidden when the sheet is opened and only when a value appears in E24 should the button appear.

Could someone please point out my mistake and how to fix it?

 

[mumps]

Change the button name to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Cells(5, 24).Value <> "1" Then
        ActiveSheet.Shapes.Range(Array("Button 412")).Visible = True
    Else
        ActiveSheet.Shapes.Range(Array("Button 412")).Visible = False
    End If
    Application.ScreenUpdating = True
End Sub

 

[TheJay]

Thanks for your reply. I've added it but it's not yet working as expected.

In E24, there is a formula

=IFERROR(IF(ISBLANK(D4),"",(SUM(E4:E23)))," ")

Perhaps I need to refine the code? It's looking for an amount of time.

 

[mumps]

If you are saying that you want to hide/unhide the button depending on the value in E24 and the value of E24 is the result of a formula, then try:

Private Sub Worksheet_Calculate()
    If Range("E24") <> 1 Then
        ActiveSheet.Shapes.Range(Array("Button 412")).Visible = True
    Else
        ActiveSheet.Shapes.Range(Array("Button 412")).Visible = False
    End If
End Sub

 

[TheJay]

Thanks for your reply.

I've updated the button name and adapted the code but it's not hiding the button.

Private Sub Worksheet_Activate()
    Range("B4").Select
    With Worksheets("Tracker")
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub

Private Sub Worksheet_Calculate()
    If Range("E24") <> 1 Then
        ActiveSheet.Shapes.Range(Array("Save")).Visible = True
    Else
        ActiveSheet.Shapes.Range(Array("Save")).Visible = False
    End If
End Sub

Before updating the button name it didn't work either.

I've not seen Worksheet_Calculate before. Will it initialise when the worksheet is opened? Also, should this code be able to cope with a time? It's a cumulative figure, so it would appear as 7:24 for example.

 

[mumps]

Worksheet_Calculate triggers the macro whenever a formula in the sheet is calculated. If you want it to trigger when the sheet is opened, then place the code in the Worksheet_Activate event. What kind of button are you using? Is it a Form Control button, ActiveX button or a simple shape?

 

[TheJay]

Thanks for your reply. It's a form control button. So if I want this to work correctly, I need to insert in activate AND include calculate, because it's expected the user will input data after opening the sheet. The formula I included before doesn't produce a value until the user enters data into three cells.

 

[mumps]

Since the hide/unhide depends on the value of E24 which is the result of a formula, you need to use the Worksheet_Calculate event. You don't need the Worksheet_Activate event unless activating the sheet causes the value of E24 to change. If it's still not working, perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary).

 

[TheJay]

Sure, we'll try that next. From your questioning, you got me wondering whether "Shapes" was the right thing to reference.

Private Sub Worksheet_Calculate()
    If Range("E24") <> 1 Then
        ActiveSheet.Buttons("Save").Visible = True
    Else
        ActiveSheet.Buttons("Save").Visible = False
    End If
End Sub

I tried this but it's still not working. Have I done this right?

 

[mumps]

Yes. If I could see your file, it would help.