Problem with ActiveX control
On Oct 18, 7:06 pm, Gief Hell wrote:
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
|