View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Peter T Peter T is offline
external usenet poster
 
Posts: 5,600
Default Embed Command Button in Cell

Following should size your control to a cell and set its "Move and Size"
property

Sub test()
Dim obj As Object

Set obj = Selection ' eg, Forms button, rectangle etc

' maybe a Controls Toolbox button
'Set obj = ActiveSheet.OLEObjects("CommandButton1")

' some shape
' Set obj = ActiveSheet.Shapes("Rectangle 6")

If TypeName(obj) = "Shape" Then Set obj = obj.DrawingObject

SizeToCell obj, Range("D4") ' optionally set to a cell

End Sub

Sub SizeToCell(dwo As Object, Optional rTopLeft As Range)
Dim c As Range
With dwo
If rTopLeft Is Nothing Then
Set c = .TopLeftCell
Else
Set c = rTopLeft
End If
.Left = c.Left
.Top = c.Top
.Width = c.Width
.Height = c.Height
.Placement = xlMoveAndSize
End With
End Sub


Regards,
Peter T

"Bythsx-Addagio" wrote in message
...
Hello,
I would like to set a command button or other control inside a worksheet
cell. Essentially I am hoping ot have a clearly identifable button which
sits in the space of a particular cell. Does anyone know if this is
possible?

Thanks for your help!