Change button type, caption and assign macro

[cgcamal]

Hi everyone,

I have several "shapes" buttons (ActiveX control) in Sheet1 and I'm trying to replace each one of those buttons with
"normal" buttons (Userform control) using the same position (height, width, left, top), same Caption text of the existing
buttons, and assign all new buttons to the same "Macro1".

I'm trying with:

Sub Change_Buttons_Type()
Dim Shps As Shape, sh As Integer

For Each Shps In ActiveSheet.Shapes
    sh = sh + 1

    With Shps
        If .OLEFormat.Object.Name Like "CommandButton*" Then
            ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height).OnAction = "Macro1"
            ActiveSheet.Shapes.Range(Array([B][COLOR=Red]CurrentButton[/COLOR][COLOR=Red]Added[/COLOR][/B])).Characters.Text = [COLOR=Red][B].Caption
            [/B][/COLOR]ActiveSheet.Shapes.Range(Array(.OLEFormat.Object.Name)).Delete
        End If
    End With
Next
End Sub

Because my code is not finished, I think is correct except parts in red. I don't know how to say that the current
control created take the caption of the current shape that will be deleted.

I hope someone could help me with this.

Thanks in advance.

Regards

 

[Norie]

Cesar

I don't quite follow.

You have some ActiveX 'shapes buttons' which you want to replace with 'userform' command buttons.

How exactly did you create the original 'buttons'?

As far as I know there isn't an ActiveX 'shape' object, not in the default toolbox.

Also, aren't 'userform' buttons ActiveX?

 

[Andrew Poulsom]

Try:

Sub Change_Buttons_Type()
    Dim Cmd As OLEObject
    Dim Btn As Button
    For Each Cmd In ActiveSheet.OLEObjects
        With Cmd
            If TypeName(.Object) = "CommandButton" Then
                Set Btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
                With Btn
                    .OnAction = "Macro1"
                    .Caption = Cmd.Object.Caption
                End With
                Cmd.Delete
            End If
        End With
    Next Cmd
End Sub

 

[cgcamal]

Hey Andrew,

Thanks for your help. It seems it works just great.

Thanks again for share your knowledge and help.

Norie,

Thanks for reply.

I inserted the ActiveX buttons exactly as is described in link below in the part "Add a command button (ActiveX control)".

http://office.microsoft.com/en-us/e...a-macro-to-it-in-a-worksheet-HP010342137.aspx

Thanks again.

Regards.