CODEWITHSUNDEEP
excelvbabuttonhide
M B
M B

Reputation: 77

Excel VBA hiding a button based on a cell value

I've got a 500 row spreadsheet with a form "Properties" button in the M column of each row.

The button works great and performs the macro when clicked, showing information for that row.

I copied the button onto the same column of the 500 row spreadsheet so there is a button on every row. But what I really want is if the cell on the A column of that row is empty, the button is hidden. Is there a way to do that?

For reference I added the code below the button uses when clicked. Currently every button in the M column references the below macro.

Sub Button17_Click()

    Dim x As Variant
    Dim y As Variant
    ' Find Row and Column of clicked button
    Dim B As Object, csNew As Integer, rsNew As Integer
    Set B = ActiveSheet.Buttons(Application.Caller)
    With B.TopLeftCell
        csNew = .Column
        rsNew = .Row
    End With
    'Find out if the row item is "SWGR/MCC/XFMR"
    x = Sheets("Worksheet").Cells(rsNew, csNew + 17).value
    y = Sheets("Worksheet").Cells(rsNew, csNew - 11).value
    If x = "SWGR" And y = "XFMR" Then
        UserForm1.Show
    ElseIf x = "MCC" And y = "XFMR" Then
        UserForm2.Show
    ElseIf x = "MCC" Then
        UserForm3.Show
    ElseIf x = "SWGR" Then
        UserForm4.Show
    End If
Debug.Print "Button initialized"

End Sub

Upvotes: 0

Views: 1247

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Something would need to trigger the code to hide/unhide the buttons, so try a worksheet_change event handler in the sheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, c As Range, b As Object
    'only interested in ColA changes
    Set rng = Application.Intersect(Target, Me.Columns("A"))
    If Not rng Is Nothing Then
        'check each changed cell
        For Each c In rng.Cells
            Set b = buttonFromCell(c)
            If Not b Is Nothing Then
                b.Visible = Len(c.Value) > 0 'visible only if has value
            End If
        Next c
    End If
    
End Sub

'Find a button on the same row as c
Function buttonFromCell(c As Range)
    Dim b As Object
    For Each b In Me.Buttons
        If b.TopLeftCell.Row = c.Row Then
            Set buttonFromCell = b
            Exit Function
        End If
    Next
    Set buttonFromCell = Nothing '<< no button found
End Function

Upvotes: 1

Related Questions