VBA How to loop through columns

Question

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

For tasks like this, Cells(rowNumber, colNumber) is easier to work with than Range() — Tim Williams
– Tim Williams, Commented Mar 21, 2020 at 22:39

Gary's Student · Accepted Answer · 2020-03-21 23:17:28Z

1

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.

edited Mar 21, 2020 at 23:17

answered Mar 21, 2020 at 23:08

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jorge Sanchez Over a year ago

Thanks, that answers my question! Although I ended up using For i = 1 To 7 ActiveSheet.Spinners.Add(Cells(1, i).Left, Cells(1, i).Top, 69.5, 20).Select Next

Collectives™ on Stack Overflow

VBA How to loop through columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related