excelvbacommandbuttonactivexobject

How do I hide an ActiveX Command button with VBA based on cell contents

I have an Excel 2010 sheet with dropdown lists that are linked to hidden cells. The dropdowns are not populated by default (linked cells are empty). I also have an ActiveX Command Button called "VIS" that opens a website based on user selections, but I don't want the button visible until cell J1 has a value (J1 is linked to a drop-down list). How do I make the button hidden if J1.value = "" and visible if it is not blank? If it's easier, I also have a formula in cell M1 "=IF(J1="",TRUE,FALSE)" to be able to use true/false arguments in VBA. Thanks.

Solution

  • Not saying it's the best way, but here is a way...use the worksheet_calculate event like this:

    Private Sub Worksheet_Calculate()
VIS.Visible = Not Range("M1").Value
End Sub

    I went with this method because it fits with your current sheet design and the logic you have laid out. If you'd like an alternative method, I'd be more than happy to provide one. Either way, I hope it gets you going in the right direction!

    Ok, I couldn't resist. Here's another method using the Worksheet_Change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        If Target.Address = "$J$1" Then VIS.Visible = Application.WorksheetFunction.CountA(Range("J1"))
    Application.EnableEvents = True
End Sub

    The benefit of the worksheet_change event is, you can get rid of the formula you have in M1. Depending on your sheet layout, the Application.EnableEvents = False may not be necessary..but doesn't really hurt having it in there just in case.