Need to know how to size an ActiveX button to be the same size as the cell underneath it

[Foo_Man_Chu]

Hi all,
It's been a long time since I've been in here, I'm hoping everyone will be patient with me. I'm using Excel 2013. I have an ActiveX Button control in my spreadsheet. For the life of me I can't figure out how to make the button the same size as the cell it is on top of, meaning the cell the button is in front of (if the button sized correctly you wouldn't be able to see the cell because it would be completely blocked out by the button). Any help that can be given would be very much appreciated.

 

[MickG]

Try:-

Private Sub CommandButton1_Click()
With CommandButton1
    .Top = .TopLeftCell.Top
    .Left = .TopLeftCell.Left
    .Width = .TopLeftCell.Width
    .Height = .TopLeftCell.Height
End With
End Sub

 

[Foo_Man_Chu]

Thanks for the quick reply MickG. Unfortunately that's not exactly what I want. I need to create the button on the spreadsheet and have the button cover the cell exactly before the spreadsheet even runs, not have it resized based off of a button click event. Anyone else? Thanks

 

[MickG]

Try this:-
NB:- Change range "R" to suit.

[COLOR="Navy"]Sub[/COLOR] MG03Oct31
[COLOR="Navy"]Dim[/COLOR] nBut [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] R = Range("B2")
[COLOR="Navy"]Set[/COLOR] nBut = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=103.8, Top:=51, Width:=72, Height:=24)
        nBut.Top = R.Top
        nBut.Left = R.Left
        nBut.Width = R.Width
        nBut.Height = R.Height
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick