CODEWITHSUNDEEP
excelvba
Jorge Sanchez
Jorge Sanchez

Reputation: 5

VBA How to loop through columns

This code adds a spinner button in each row. How could I write it to add a spinner button in each column... Lets say I want to add a spinner button in A1, B1, C1... G1.

Dim i As Integer
For i = 1 To 7
    ActiveSheet.Spinners.Add(Range("A" & i).Left, Range("A" & i).Top, 69.5, 20).Select
Next

Upvotes: 0

Views: 376

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

If you loop over the elements of a range, it does not matterr if you traverse columns or rows. Here is a Button example:

Sub BoxAdder()
    Dim rng As Range, r As Range, bt As Button, s As Shape
    Set rng = Range("A1:G1")

    For Each r In rng
        ActiveSheet.Buttons.Add(94.5, 75.75, 51, 27.75).Select
        Set bt = Selection
        bt.Characters.Text = r.Address(0, 0)
        Set s = ActiveSheet.Shapes(Selection.Name)
        With s
            .Top = r.Top
            .Left = r.Left
            .Width = r.Width
            .Height = r.Height
        End With
    Next r
End Sub

The code places buttons on cells A1, B1, C1, D1, E1. F1, and G1 sequentially. To place the buttons down a column, just change:

Set rng = Range("A1:G1")

to:

Set rng = Range("A1:A4")

Note that for the purposes of moving and sizing, I treat each Button as a Shape.

Upvotes: 1

Related Questions