Problem with ActiveX control
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 |
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 |
All times are GMT +1. The time now is 10:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com