make a macro button appear if cell is typed in

[andrewb90]

I need to make several buttons appear if a cell has text in it.
button "I1.1" should appear if anything in C2 is filled in
button "I1.2" should appear if anything in D2 is filled in...etc

Anybody know of a way to do this?

thanks,
Andrew

 

[Richard Schollar]

Hi Andrew

Assuming you are using ActiveX (not Forms) controls, try the following - you need to right click on the tab name of interest and select View Code and paste the following into the code module that will open:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, cell As Range

Set rng = Intersect(Target, Range("C2:E2"))  'extend range as needed

If Not rng Is Nothing Then
    For Each cell In rng
      Me.OLEObjects("I1." & cell.Column - Range("C2").Column + 1).Visible = (cell.Value <> "")
    Next cell
End If

End Sub