button location

[288enzo]

I would like to add a button to a sheet, this I can figure out. What I'm having difficulties with is how to place the button where I want it. I've tried the old fashioned way of recording a macro, and going through the steps of adding the button, then moving it around until I have it where I want it.
I've searched around, and all I can find are examples of placing a button in a cell. In my case, I'm trying to add three buttons in a cell starting at the top of the cell. All three the same size, one above the other with a slight space between each button.

If anyone knows of a good resource to read up on this, I would be grateful.

This is from my "Macro Record", and what makes it even more difficult is that when I try and run my recorded macro I get a run-time error. The item with the specified name wasn't found. Line three is highlighted.
I just figured out that's because when it adds a new button it will automatically give it a name, thus it will never find button 31. Grrr

    ActiveSheet.Buttons.Add(491.25, 40.5, 76.5, 22.5).Select
    Selection.OnAction = "Book1!button1"
    ActiveSheet.Shapes("Button 31").IncrementLeft -5.25
    ActiveSheet.Shapes("Button 31").IncrementTop -5.25
    ActiveSheet.Shapes("Button 31").ScaleWidth 1.2200432299, msoFalse, _
        msoScaleFromTopLeft
    Selection.Copy
    ActiveSheet.Buttons.Add(486, 35.25, 93, 22.5).Select
    ActiveSheet.Paste
    ActiveSheet.Shapes.Range(Array("Button 32")).Select
    ActiveSheet.Shapes("Button 32").IncrementLeft 59.1666929134
    ActiveSheet.Shapes("Button 32").IncrementTop 19.1666929134
    ActiveSheet.Shapes("Button 32").IncrementLeft -70
    ActiveSheet.Shapes("Button 32").IncrementTop -3.3333070866
    ActiveSheet.Shapes("Button 32").IncrementLeft -0.8333070866
    ActiveSheet.Shapes("Button 32").IncrementTop -0.8333070866
    ActiveSheet.Shapes("Button 32").IncrementTop -0.8333070866
    ActiveSheet.Shapes("Button 32").IncrementTop -0.8333070866
    ActiveSheet.Shapes("Button 32").IncrementTop -0.8333070866
    ActiveSheet.Buttons.Add(486, 35.25, 93, 22.5).Select
    ActiveSheet.Paste
    ActiveSheet.Shapes.Range(Array("Button 33")).Select
    ActiveSheet.Shapes("Button 33").IncrementLeft -11.6666929134
    ActiveSheet.Shapes("Button 33").IncrementTop 36.6666141732
    ActiveSheet.Shapes("Button 33").ScaleHeight 0.8000034996, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("Button 32")).Select
    ActiveSheet.Shapes("Button 32").ScaleHeight 0.8740682415, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes.Range(Array("Button 31")).Select
    ActiveSheet.Shapes("Button 31").ScaleHeight 0.9259247594, msoFalse, _
        msoScaleFromTopLeft

Here I was hoping to show you what the buttons looked like.

2021_CertReadinessReport_ASE_AudiSouthern_10132021.xls
	F
3	Total New Hires Incomplete ABE
Dealer Status

 

[GWteB]

You probably could start with something like this ...

Public Sub EnzoButtons()

    Dim Btn1 As Button, Btn2 As Button, Btn3 As Button
    Dim Rng As Range

    With ActiveSheet
        Set Rng = .Range("A1")                  ' <<< change cell to suit
        Set Btn1 = .Buttons.Add(1, 1, 1, 1)
        Set Btn2 = .Buttons.Add(1, 1, 1, 1)
        Set Btn3 = .Buttons.Add(1, 1, 1, 1)
    End With

    With Rng
        Btn1.Height = .Height / 3 - 1
        Btn2.Height = .Height / 3 - 1
        Btn3.Height = .Height / 3 - 1

        Btn1.Width = .Width - 2
        Btn2.Width = .Width - 2
        Btn3.Width = .Width - 2

        Btn1.Left = .Left + 1
        Btn2.Left = .Left + 1
        Btn3.Left = .Left + 1

        Btn1.Top = .Top + 1
        Btn2.Top = Btn1.Top + Btn1.Height + 1
        Btn3.Top = Btn2.Top + Btn2.Height + 1

    End With
End Sub

 

[288enzo]

You probably could start with something like this ...

Public Sub EnzoButtons()

    Dim Btn1 As Button, Btn2 As Button, Btn3 As Button
    Dim Rng As Range

    With ActiveSheet
        Set Rng = .Range("A1")                  ' <<< change cell to suit
        Set Btn1 = .Buttons.Add(1, 1, 1, 1)
        Set Btn2 = .Buttons.Add(1, 1, 1, 1)
        Set Btn3 = .Buttons.Add(1, 1, 1, 1)
    End With

    With Rng
        Btn1.Height = .Height / 3 - 1
        Btn2.Height = .Height / 3 - 1
        Btn3.Height = .Height / 3 - 1

        Btn1.Width = .Width - 2
        Btn2.Width = .Width - 2
        Btn3.Width = .Width - 2

        Btn1.Left = .Left + 1
        Btn2.Left = .Left + 1
        Btn3.Left = .Left + 1

        Btn1.Top = .Top + 1
        Btn2.Top = Btn1.Top + Btn1.Height + 1
        Btn3.Top = Btn2.Top + Btn2.Height + 1

    End With
End Sub

That worked well, but I have text in the cell I don't want to cover. I'm trying to get all three buttons in the area above the text while staying in the cell.

2021_CertReadinessReport_ASE_AudiSouthern_10132021.xls
	F
3	Total New Hires Incomplete ABE
Dealer Status

I suppose I could split the row, then your script would work. Does that sound easier then what the alternative may be?
Thank you

 

[GWteB]

I suppose I could split the row

That would be the easiest way, otherwise you've to put some additional calculations in the code to position the buttons to your likings.