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
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