Refering to an object with a variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cory - 28 Sep 2006 17:05 GMT

I have a userform with a number of Command Buttons (Button1, Button2,
etc...). Before the form loads, I would like to change the caption of each
of the buttons (with the use of a loop) to the contents of certain cells.
Can anyone help me with the syntax is to do so. For example:

For x = 1 to 20
ProjectForm.("Button" & x).Caption = Sheet1.Cells(2, x + 2)

Reply to this Message

Tom Ogilvy - 28 Sep 2006 17:59 GMT

That is pretty much it. You just need to package it in the initialize event
in the userforms code module

Private Sub Userform_Initialize()
Dim x as Long
Dim cb as MSForms.CommandButton
for x = 1 to 20
set cb = me.Controls("Button" & x)
cb.Caption = Sheet1.Cells(2, x + 2).Value
Next
end sub

Signature

Regards,
Tom Ogilvy

> I have a userform with a number of Command Buttons (Button1, Button2,
> etc...). Before the form loads, I would like to change the caption of each
[quoted text clipped - 3 lines]
> For x = 1 to 20
> ProjectForm.("Button" & x).Caption = Sheet1.Cells(2, x + 2)

Reply to this Message

JMB - 28 Sep 2006 18:17 GMT

Try

For x = 1 To 20
ProjectForm.Controls("Button" & x).Caption = Sheet1.Cells(2, x + 2)
Next x

I'm assuming "Button" refers to the commandbutton object names, not the
current caption(s). Unless they changed it in subsequent versions, VBA's
default name for its commandbuttons are CommandButton1, CommandButton2, etc.
If you get an object not found error - you'll need to verify this.

Reply to this Message