excel vb loop/concatenate - what am Idoing wrong

Question

I wanted to make all my btn invisible. They are named:

btnHere1

btnHere2

btnHere3

btnHere4

For i = 1 To 4 ["btnHere" & i].Visible = False Next

Answer 1

for i = 1 to 4 : me.controls("btnHere" & i).visible = false : next i

If run from outside of form, replace Me by a form object reference.

Answer 2

One way to do this is to loop through all controls in your form:

Dim ctrl As Control

For Each ctrl In Me.Controls

    If TypeName(ctrl) = "CommandButton" Then
        ctrl.Visible = False
    End If

Next ctrl

This technique means that you don't need to reference your buttons by name as in your code example.

Hope this helps.

Answer 3

If you have buttons on the spreadsheet itself as opposed to a form they are actually shapes, although you can still name them btnSomethingOrOther.

If you wanted to make these invisible then just iterate through the shapes on sheet...

For Each control In ActiveSheet.Shapes
   If Mid(control.Name, 1, 3) = "btn" Then
       control.Visible = False
   End If
Next

However If you do have an actual form you are using then the first answer will do the trick fine as well.