(Solved) – Define sheets of MoveButton function in VBA

(solved)-–-define-sheets-of-movebutton-function-in-vba

I have an Excel file consisting of 5 Sheets.
In Sheet1, Sheet2 and Sheet3 I have a Button 1.

Once the user clicks on one of those buttons all Button 1 should move from their current position to range D9:E11.
No matter in which sheet the user clicks the button.

I tried to go with the solution from here but it only works if I have two sheets.
I tried to add Sheet3 but it did not work:

wrong number of arguments or invalid property assignment

Sub Sample()
    MoveButton Sheet1, "Button 1", Sheet2, Sheet3
End Sub

Sub MoveButton(sh As Worksheet, btnName As String, Optional shB As Worksheet)
    Dim Range_Position As Range

    Set Range_Position = sh.Range("D9:E11")

    With sh.Buttons(btnName)
        .Top = Range_Position.Top
        .Left = Range_Position.Left
        .Width = Range_Position.Width
        .Height = Range_Position.Height
        .Text = "Button 1"
    End With

    If Not shB Is Nothing Then
        With shB.Buttons(btnName)
            .Top = Range_Position.Top
            .Left = Range_Position.Left
            .Width = Range_Position.Width
            .Height = Range_Position.Height
            .Text = "Button 1"
        End With
    End If
End Sub

What do I need to change in the code to specify each sheet in which I want to move the Button 1 to range D9:E11?

Leave a Reply

Your email address will not be published. Required fields are marked *