Making a command button the EXACT size of a cell

[phantom1975]

What can be done to make a command button fit PERFECTLY inside of one cell?

 

[Derek]

Hi there

Try this.
Stretch your command button so that it exactly coincides with the cell boundaries. Now right click the button, select Format Control, select the Properties tab, select Move and Size with cells. Now if you alter the width of your columns or rows the button will keep to the same size as the cell it occupies.

Hope this helps
Derek

 

[miguel.bartolomeo]

Hi,

Make sure you know what are the cell`s Width and Height. ( You can do this by right clicking on the far top or the far left of the Column/Row)

Right click on the command button, chose properties and in there you will have all informatio about the command button, including Size.

Good luck

 

[Tom Urtis]

Hey Billy - -

Assuming this is a non-VBA question, you can "snap" any shape such as a Command Button in a cell by dragging one of its corner handles (example top left) while in Design mode and (here's the trick) pressing the Alt key. Then do the same thing with the opposite corner handle (example bottom right), again, while holding down the Alt key.

Release the Alt key, set the property for Move and Size with cells, and exit Design mode.


If this is a VBA question, then

If this is a Forms button you need to create and fit into cell C5 for example:

Sub CreateFormsButton()
Dim btn As Button
Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range("C5")
Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With btn
.Caption = "Test"
.OnAction = "Test1"
End With
End With
End Sub

Else if this is an activex commandbutton you need to create and fit into C10 for example, and write event code for it at the same time:

Sub CreateCommandButton()
Dim ctop#, cleft#, cht#, cwdth#
Dim sht As Worksheet
Dim Btn As OLEObject
Set sht = ThisWorkbook.Worksheets("Sheet1")
With Range("C10")
ctop = .Top
cleft = .Left
cht = .Height
cwdth = .Width
End With
With sht
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=cleft, Top:=ctop, Width:=cwdth, Height:=cht)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "MyButton"
Btn.Placement = xlMoveAndSize
'Optional code insertion - - establish ref in VBE to MS VBA Extensibility 5.3 Library
With ThisWorkbook.VBProject.VBComponents(sht.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, "Msgbox ""Replace this message with your actual code."" "
End With
End Sub

 

[jamescooper]

This works nicely, thanks.

 

[knowak1968]

Hi Tom - I've never seen a variable declaration with written with a # at the end. What does that mean?
Dim ctop#, cleft#, cht#, cwdth#

 

[dave3009]

Hi Tom - I've never seen a variable declaration with written with a # at the end. What does that mean?
Dim ctop#, cleft#, cht#, cwdth#

The hashtag means double.

VBA Variables - Abbreviations

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com