Using a variable to select a CommandButton and setting the caption

[PCLIVE]

I'm using a CommandButton from the Control Toolbox. How can I access this
control via VBA using a Variable.

Example:
i=1

I have CommandButton1.

I'd like to set the caption of CommandButton1 use the "i" variable for the
number of that CommandButton. I've done this before with a button on a
UserForm using something like:

UserForm3.Controls("CommandButton" & i).Caption = "Test"

So how can I do this with a CommandButton that is not on a UserForm?

Thanks,
Paul



--

 

[redeagle]

Hi Paul-

I know that a control on a worksheet is actually a member of the Shapes
collection (not the Controls collection). So you can loop through the shapes
and I tried:

Dim s As Shape
Dim cb As CommandButton

For Each s In Sheet1.Shapes
If s.Name = "CommandButton" & i Then
'Set cb = s
'cb.Caption = "Test"
End If
Next

But the Set statement doesn't work and that's why I commented it out...

So I guess that is a partial answer. Sorry that isn't much help.

John

 

[Rick Rothstein]

See if this example gets you started...

Worksheets ("Sheet1")
X = 1
.OLEObjects("CommandButton" & X).Object.Caption = "First Button"
X = 2
.OLEObjects("CommandButton" & X).Object.Caption = "Second Button"
End With

 

[PCLIVE]

YES! That's what I was looking for.

Thanks.
Paul

--

See if this example gets you started...

Worksheets ("Sheet1")
X = 1
.OLEObjects("CommandButton" & X).Object.Caption = "First Button"
X = 2
.OLEObjects("CommandButton" & X).Object.Caption = "Second Button"
End With

 

[PCLIVE]

Rick, Thanks for your help on the previous question.
since you know just where I was coming from on that question, I'm tagging
this one on too.

When I press a CommandButton, is there a way to capture the caption
information of a command button that was just pressed and have it displayed
in, say AA1? I'd like this to happen without knowing which CommandButton
I'm pressing.

Range("AA1").Value= Pressed Command Button Caption

Is this possible?

Thanks,
Paul



--

See if this example gets you started...

Worksheets ("Sheet1")
X = 1
.OLEObjects("CommandButton" & X).Object.Caption = "First Button"
X = 2
.OLEObjects("CommandButton" & X).Object.Caption = "Second Button"
End With