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

Foo_Man_Chu

Board Regular
Joined
Jul 22, 2010
Messages
79
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try:-
Code:
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

Board Regular
Joined
Jul 22, 2010
Messages
79
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
NB:- Change range "R" to suit.
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,255
Messages
5,467,573
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top