Macro to hide shapes of they have no data in them?

[tonywatsonhelp]

Hi Everyone,
I want to set up a set of shapes that can operate like buttons,
The idea is you choose a make from a drop down, then you get a grid of options to press that are all shapes each with a different option,

So What i have are 70 shapes call Bttn1 to Bttn70
each shape is linked to a cell,
I only want the shape to be visable if the cell contains a word,
the cells are a grid not just a row or column,
so I feel I making this more complex than it needs to be,

So Data is in range O12:U22 (with O12 being data for Bttn1, U22 Bttn70 going right to left)

So All I need is a macro that says goto through each cell in range O12:U22 if cell is empty then bttn1 hidden if not bttn1 visible.

any ideas how to do this?

 

[bobsan42]

try this:

Sub testbtHide()
    Dim i As Long, j As Long, k As Long, nr As Long, nc As Long
    Dim rng As Range, cc As Range
    
    Set rng = ActiveSheet.Range("O12:U22")
    nr = rng.Rows.Count
    nc = rng.Columns.Count
    
    k = 0
    
    With ActiveSheet.Shapes
        For i = 1 To nr
            For j = 1 To nc
                k = k + 1
                Set cc = rng.Cells(i, j)
                .Range(Array("Bttn" & k)).Visible = Not IsEmpty(cc.Value)
            Next j
        Next i
    End With
    Set rng = Nothing
    Set cc = Nothing
End Sub