Problem with ActiveX control

[Gief Hell]

Hello all

In differents workbooks, I use many ActiveX control, the name of the
ActiveX controls can be example:

CommandButton1, CommandButton2, ... CommandButton7


I would like to change the caption or anything else for the Command
Button then

Worksheets(1).CommandButton1.Caption = "Title " & 1
Worksheets(1).CommandButton1.visible = True
...
Worksheets(7).CommandButton7.Caption = "Title " & 7
Worksheets(7).CommandButton7.visible = True


It's long in some situation


But I would like to use a loop to do the same operation for many
ActiveX control,
and I don't how and Why, I try different things, like :

For X = 1 To 7

Worksheets(X).Shapes("CommandButton " & X).Visible = True
Worksheets(X).Shapes("CommandButton " & X). Caption = "Title
" & 1

Next X

Sometimes the operation run, other time no

Execution error '-2147024809 (80070057)':


Could you help me to understand how to use ActiveX control ?


Thanks

 

[Leith Ross]

Hello all

In differents workbooks, I use many ActiveX control, the name of the
ActiveX controls can be example:

CommandButton1, CommandButton2, ... CommandButton7

I would like to change the caption or anything else for the Command
Button then

Worksheets(1).CommandButton1.Caption = "Title " & 1
Worksheets(1).CommandButton1.visible = True
...
Worksheets(7).CommandButton7.Caption = "Title " & 7
Worksheets(7).CommandButton7.visible = True

It's long in some situation

But I would like to use a loop to do the same operation for many
ActiveX control,
and I don't how and Why, I try different things, like :

For X = 1 To 7

Worksheets(X).Shapes("CommandButton " & X).Visible = True
Worksheets(X).Shapes("CommandButton " & X). Caption = "Title
" & 1

Next X

Sometimes the operation run, other time no

Execution error '-2147024809 (80070057)':

Could you help me to understand how to use ActiveX control ?

Thanks

Hello Gief Hell,

Here is a macro to relabel all the command buttons on the active
sheet. This code can be changed and used for any ActiveX control.

Sub ReLabel()

Dim nItem
Dim Obj As Object
Dim X As String

For Each Obj In ActiveSheet.OLEObjects
X = TypeName(Obj.Object)
If X = "CommandButton" Then
With Obj.Object
nItem = Replace(.Caption, X, "")
.Caption = "Title " & nItem
End With
End If
Next Obj

End Sub

Sincerely,
Leith Ross