Changing multiple button texts in a loop?

[steve2008]

Hi, I have 120 buttons on a form named commandbutton1 - commandbutton120

I have an Excel 97 sheet which will contain the texts I would like displayed on these buttons.

On opening I would like these values to be pasted into the buttons.

I can do this fine by writing 120 lines of code but was hoping there would be a way to do it in a for loop.

something like this (Note the code is wrong, i'm just trying to explain my aim.)

 for i=1 to 120
 commandbutton & i . value = activecell.value
 range("activecell.offset(0,1)").select
next

I have several attributes I wish to assign for each button, so would have to edit a lot of lines if this is not possible.

 

[JamesW]

Hi,

Try:

    For Each cb In Me.Controls
        cb.Caption = ActiveCell.Value
        ActiveCell.Offset(0, 1).Select
    Next

 

[JamesW]

Edit:

Forgot to add a check to see if it's a command button, otherwise it will change everything.

    For Each cb In Me.Controls
        If TypeName(cb) = "CommandButton" Then
            cb.Caption = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
        End If
    Next

 

[RoryA]

Since you have a naming convention:

for i=1 to 120
Me.Controls("commandbutton" & i).Caption = activecell.offset(0, i - 1).value
next i

 

[steve2008]

Thanks both, I have it working

I went with Rorya's suggestion as I understand the code better.

 

[steve2008]

I have added more command buttons of the same type at the bottom of the main list of 120. but they are for a different form.

Currently I have it coded like

for i=1 to 120
Till("commandbutton" & i).Caption = activecell.offset(0, i - 1).value
next i

where Till is the name of my form.
The me.controls command doesn't work here, but I would like it to, then would it change the rest of the buttons on the other forms just by changing i's range?

Alternatively I have been trying to create a sub routine which would get passed the forms name and the button number and set that button up.
something like the below, but that doesn't work.

sub buttonsetup ( byval formid as string, byval buttonid as integer)

formid("commandbutton" & buttonid).Caption 
   = activecell.offset(0, buttonid - 1).value
end sub

with the current codded section changing to

for i=1 to 120
buttonsetup till, i
next i

 

[RoryA]

It would be:

Till.controls("commandbutton" & I).caption = ...