PDA

View Full Version : Solved: Can vba code create and Activex CommandButton and set its properties



frank_m
05-06-2011, 12:21 AM
If I turn on the macro recorder and create an activex command button, I end up with what I expect. A button on my active sheet that I can right click on and select view code, or select and change properties, etc.

But if I run the macro, I end up with a button that does not have the same right click menu... (view code, and properties are missing.) It seems to be the same or nearly the same as a forms type sheet button.

Is there any way that I can change the code so that I'll end up with a standard activex command button on my sheet?
Sub Macro1()

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=366, Top:=113.25, Width:=200.25, Height _
:=39.75).Select

End Sub Below is what I have so far.
Sub CreateActiveXButton()

Dim iLeft As Integer, iTop As Long, iWidth As Integer, iHeight As Integer
Dim shp As Object
iTop = ActiveCell.Top
iHeight = ActiveCell.Height * 2
iLeft = ActiveCell.EntireRow.Cells(3).Left
iWidth = ActiveCell.EntireRow.Cells(3).Resize(, 3).Width

For Each shp In ActiveSheet.Shapes

If Left(shp.Name, 13) = "CommandButton" And shp.Type = 12 Then

shp.Delete
End If
Next shp

If ActiveCell.Row > 1 And Selection.Rows.Count = 1 Then

Set shp = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=iLeft, Top:=iTop, Width:=iWidth, Height:=iHeight)

With shp
'.TakeFocusOnClick = False 'button won't have any code & This is the only property I need
End With

End If

End Sub Thanks

frank_m
05-06-2011, 01:03 AM
I'm going to mark this solved because I got it to do what I need by adding .Object
shp.Object.TakeFocusOnClick = False

Still though for future reference I would like to know if I can create an activex commandbutton with code that looks and acts as I would expect.

-- Thanks
Sub Create_ActiveXButton()

Dim iLeft As Integer, iTop As Long, iWidth As Integer, iHeight As Integer
Dim shp As Object
iTop = ActiveCell.Top
iHeight = ActiveCell.Height * 2
iLeft = ActiveCell.EntireRow.Cells(3).Left
iWidth = ActiveCell.EntireRow.Cells(3).Resize(, 3).Width

For Each shp In ActiveSheet.Shapes

If Left(shp.Name, 13) = "CommandButton" And shp.Type = 12 Then

shp.Delete
End If
Next shp

If ActiveCell.Row > 1 And Selection.Rows.Count = 1 Then

Set shp = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=iLeft, Top:=iTop, Width:=iWidth, Height:=iHeight)

shp.Object.TakeFocusOnClick = False 'added .Object to solve my need

End If

End Sub

Bob Phillips
05-06-2011, 01:27 AM
I haven't noticed that before, but why would you need it particularly, you can see the properties in the code window.

frank_m
05-06-2011, 02:22 AM
Hi Bob,

Yes, that is simple enough... Thanks.