How to loop through CommandButton on Sheet1 with For next loop

Question

So I want to set Caption on Command buttons according to A1 to D1 strings in sheet3

Dim buttonname as String
Dim i as Integer

For i = 1 to 5 
buttonname = "Commandbutton" & i

buttonname.Caption = Worksheets(sheet3).Cells(1,i)

Next

It looks like this code is not working, need to be Controls but when I tried with Controls I can not set it to change command buttons

Chronocidal · Accepted Answer · 2019-04-09 16:12:22Z

1

buttonname is a String, not an Object. You first need to find the Object, and then change the Caption / Text

This will, however, vary based on whether you are using a Form Control, or an ActiveX Control:

'ActiveX Control
Worksheets("sheet3").OLEObjects(i).Object.Caption = buttonname
'Form Control
Worksheets("sheet3").Shapes(i).OLEFormat.Object.text

answered Apr 9, 2019 at 16:12

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

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

2 Comments

jure Over a year ago

To be honest I don't get this? Should I change buttonname to Object? And I am using ActiveX Control

Chronocidal Over a year ago

@jure Try Worksheets("sheet3").OLEObjects(buttonname).Object.Caption = Worksheets(sheet3).Cells(1,i).Value to set the Caption of the OLEObject with the name stored in buttonanme to the value stored in Row i, Column 1

Ricardo A · Accepted Answer · 2019-04-10 14:31:22Z

0

This is one way of many, loop all shapes in the worksheet, check for buttons, rename.

Sub listButtons()
    Dim shp As Shape
    Dim row As Long
    For Each shp In ThisWorkbook.Sheets("Sheet1").Shapes
        If shp.Name Like "Button*" Then
            row = row + 1
            shp.OLEFormat.Object.Text = Range("A" & row).Value
        End If
        'Select Case as an Alternative to the If approach
        Select Case shp.Name
            Case "Button 2"
                shp.OLEFormat.Object.Text = ThisWorkbook.Sheets("Sheet1").Range("A2").Value
            Case "Button 3"
                shp.OLEFormat.Object.Text = ThisWorkbook.Sheets("Sheet1").Range("A3").Value
            Case Else
                'Do nothing if not Button 2 or 3
        End Select
    Next
End Sub

edited Apr 10, 2019 at 14:31

answered Apr 9, 2019 at 16:31

Ricardo A

1,8151 gold badge14 silver badges17 bronze badges

2 Comments

jure Over a year ago

How will I know witch button I am on. I want to address CommandButton2 to be from A2 and so on.

Ricardo A Over a year ago

shp.Name will give you the button name, usually Button 2 will be CommandButton2, and so on. So you can do a Select Case instead of the If

Collectives™ on Stack Overflow

How to loop through CommandButton on Sheet1 with For next loop

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related