Excel VBA - Change button visibility based on update to adjacent cell

Question

I have data in column "AK" and a button in Column "AL"; there are several hundred rows and there is only one macro for all buttons as it uses relative references based on the row it is in.

I want the button to only be visible when there is data in the adjacent cell. The following pseudo-code explains what I am trying to achieve:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 37 Then
        If Target.Value = 0 Then
            Shapes(Target.offset(0, 1)).Visible = False
        Else
            Shapes(Target.offset(0, 1)).Visible = True
        End If
    End If
End Sub

The reason for doing this is that the value in AK is calculated based on other values and only displays once all mandatory fields have been completed. The button should only be available for an automation task once all details are complete. What real code would make this work without having to call each button out individually?

score 1 · Accepted Answer · edited May 23 '17 at 10:34

I'm not sure if you can directly reference a shape by its location on the sheet.
This code will look at each shape until it finds the one to the right of the cell you've just changed, it will then change the visibility based on the contents of the cell.
(Target.Value <> "") returns TRUE/FALSE.
This will only work if your buttons are placed in the correct cell (slightly too high and it will return the cell above).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim shp As Shape

    For Each shp In ThisWorkbook.Worksheets("Sheet1").Shapes
        If shp.TopLeftCell.Address = Target.Offset(, 1).Address Then
            shp.Visible = (Target.Value <> "")
            Exit For 'Exit the loop - the correct button has been found.
        End If
    Next shp

End Sub

Edit:
I've updated the code so it checks that only a single cell has been changed and then looks at each dependent cell of the cell that was changed.
This will probably muck up if the dependent cell is on another sheet though.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rUpdated As Range
    Dim shp As Shape
    Dim rCell As Range

    If Target.Cells.Count = 1 Then
        'Hopefully someone will have better code than On Error....
        On Error Resume Next
        Set rUpdated = Range(Target.Dependents.Address)
        On Error GoTo 0
        If Not rUpdated Is Nothing Then
            'Look at each dependent cell in rUpdated.
            For Each rCell In rUpdated
                'Look at each shape in the sheet and cross-reference with rCell.
                For Each shp In Target.Parent.Shapes
                    If shp.TopLeftCell.Address = rCell.Offset(, 1).Address Then
                        shp.Visible = (Target.Value = 0)
                        Exit For 'Exit the loop - the correct button has been found.
                    End If
                Next shp
            Next rCell
        End If
    End If

End Sub

NB: I got the idea for checking the dependent cell from here: How can I run a VBA code each time a cell get is value changed by a formula?

Thanks! It works so long as the Target is updated manually and so long as a single cell is updated. If I update multiple cells then it doesn't work, but that's no biggie. Also I wanted the button to update based on a calculated cell, i.e. a cell that updates based on the value of other cells; is that possible? — fileinsert, Sep 21 '16 at 10:55
Check the updated code - it will fire only if a single cell is changed and works with the calculated cell. — Darren Bartrup-Cook, Sep 21 '16 at 12:33
Awesome! I had to put in a few If statements to protect it as otherwise it would get in to a bit of a tizzy and it now works well. — fileinsert, Sep 21 '16 at 20:09

Excel VBA - Change button visibility based on update to adjacent cell

1 Answers1

Linked