Please help. :-)

I need to create 'Command Buttons' to reside on a worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'

thank you

--
Jeff

Jeff,

Here's what I come up with by running the Macro Recorder and a little
tweaking:

Sub make_buttons()
Dim cbutton As OLEObject

Set cbutton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75, Width:=49.5,
Height:=33.75)
Set cbutton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=258, Top:=78.75, Width:=50.25,
Height:=27.75)

End Sub

hth,

Doug

"Jeff" wrote in message
...
Please help. :-)

I need to create 'Command Buttons' to reside on a worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'

thank you

--
Jeff

Thank you Doug :-)

I need to create command buttons using VBA from the
'Control Box'. To be imbedded on a worksheet.
I won't be using Forms
--
Jeff


"Doug Glancy" wrote:

Jeff,

Here's what I come up with by running the Macro Recorder and a little
tweaking:

Sub make_buttons()
Dim cbutton As OLEObject

Set cbutton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75, Width:=49.5,
Height:=33.75)
Set cbutton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=258, Top:=78.75, Width:=50.25,
Height:=27.75)

End Sub

hth,

Doug

"Jeff" wrote in message
...
Please help. :-)

I need to create 'Command Buttons' to reside on a worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'

thank you

--
Jeff

Jeff,

When I run this macro it puts 2 buttons from the Control Toolbox on the
active worksheet. It sounds like that's what you want. Does it not do that
for you?

hth,

Doug


"Jeff" wrote in message
...
Thank you Doug :-)

I need to create command buttons using VBA from the
'Control Box'. To be imbedded on a worksheet.
I won't be using Forms
--
Jeff


"Doug Glancy" wrote:

Jeff,

Here's what I come up with by running the Macro Recorder and a little
tweaking:

Sub make_buttons()
Dim cbutton As OLEObject

Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75, Width:=49.5,
Height:=33.75)
Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=258, Top:=78.75, Width:=50.25,
Height:=27.75)

End Sub

hth,

Doug

"Jeff" wrote in message
...
Please help. :-)

I need to create 'Command Buttons' to reside on a worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'

thank you

--
Jeff

Works great Doug.

Can a Loop be used to create numbers of command buttons.
I seach a column for various strings in a Row and create the
number buttons based on the count of strings

For example A1 = Procedure
A22 = Procudure

the count for creating the buttons would be two for the worksheet
for 'Procecure' in column A.

Thank you for your kindness in helping.

Jeff :-)
--
Jeff


"Doug Glancy" wrote:

Jeff,

When I run this macro it puts 2 buttons from the Control Toolbox on the
active worksheet. It sounds like that's what you want. Does it not do that
for you?

hth,

Doug


"Jeff" wrote in message
...
Thank you Doug :-)

I need to create command buttons using VBA from the
'Control Box'. To be imbedded on a worksheet.
I won't be using Forms
--
Jeff


"Doug Glancy" wrote:

Jeff,

Here's what I come up with by running the Macro Recorder and a little
tweaking:

Sub make_buttons()
Dim cbutton As OLEObject

Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75, Width:=49.5,
Height:=33.75)
Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=258, Top:=78.75, Width:=50.25,
Height:=27.75)

End Sub

hth,

Doug

"Jeff" wrote in message
...
Please help. :-)

I need to create 'Command Buttons' to reside on a worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'

thank you

--
Jeff

Jeff,

You're welcome.

This counts the number of "Procedures" in column A and puts buttons at the
top of column B, one for each cell starting in B2:

Sub make_buttons()
Dim i As Long
Dim cbutton As OLEObject

For i = 1 To WorksheetFunction.CountIf(Range("A:A"), "Procedure")
With ActiveSheet.Cells(i, 2)
Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="forms.Comma ndButton.1", _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
End With
Next i

End Sub

Let me know if there's more needed.

hth,

Doug


"Jeff" wrote in message
...
Works great Doug.

Can a Loop be used to create numbers of command buttons.
I seach a column for various strings in a Row and create the
number buttons based on the count of strings

For example A1 = Procedure
A22 = Procudure

the count for creating the buttons would be two for the worksheet
for 'Procecure' in column A.

Thank you for your kindness in helping.

Jeff :-)
--
Jeff


"Doug Glancy" wrote:

Jeff,

When I run this macro it puts 2 buttons from the Control Toolbox on the
active worksheet. It sounds like that's what you want. Does it not do
that
for you?

hth,

Doug


"Jeff" wrote in message
...
Thank you Doug :-)

I need to create command buttons using VBA from the
'Control Box'. To be imbedded on a worksheet.
I won't be using Forms
--
Jeff


"Doug Glancy" wrote:

Jeff,

Here's what I come up with by running the Macro Recorder and a little
tweaking:

Sub make_buttons()
Dim cbutton As OLEObject

Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75, Width:=49.5,
Height:=33.75)
Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=258, Top:=78.75, Width:=50.25,
Height:=27.75)

End Sub

hth,

Doug

"Jeff" wrote in message
...
Please help. :-)

I need to create 'Command Buttons' to reside on a worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'

thank you

--
Jeff