Re: create command buttons using vba

Doug
Thank You from me and the Family
Your help made it possible to have time for Thanksgiving

Jeff :-)
--
Jeff


"Jeff" wrote:

Thank you Doug :-)
--
Jeff


"Doug Glancy" wrote:

Jeff,

This names them as you wanted and spaces the buttons every 8 rows:

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

For i = 1 To WorksheetFunction.CountIf(Range("A:A"), "Procedure")
With Active***.Cells(i + ((i - 1) * 7), 2)
Set cbutton =
Active***.OLEObjects.Add(ClassType:="forms.CommandButton.1", _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
cbutton.Name = "Cmd_Select" & i
End With
Next i

End Sub

Happy Thanksgiving,

Doug


"Jeff" <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:73AA87BB-7F61-4F5D-BF94-0A19BAA170BB@xxxxxxxxxxxxxxxx
Doug

Nameing is no big deal, sorry about that
However what is critical is...
I need to space down each newly created command button
by 8 rows, in the same column so it will align up with
existing data in each row.

B1 button
B9 button
B17 button
.
.
.
jeff :-)

--
Jeff


"Jeff" wrote:

Sorry about that I asked a dumb question.
It works ok as is.
I was trying to name each command button
like 'Cmd_Select1', 'Cmd_Select2', etc...
as a custom name rather than 'commandbutton1'

thx
Jeff :-)
PS
You help is appreciated
I may actually have time to run out and buy a Turkey
before the rush. :-)
--
Jeff


"Doug Glancy" wrote:

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 Active***.Cells(i, 2)
Set cbutton =
Active***.OLEObjects.Add(ClassType:="forms.CommandButton.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" <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4327E625-8B58-4AAB-9864-3D4C85CE240C@xxxxxxxxxxxxxxxx
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 work***
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 work***. It sounds like that's what you want. Does it not
do
that
for you?

hth,

Doug


"Jeff" <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3AE78420-7098-45E6-A412-C8E4CF6FCF49@xxxxxxxxxxxxxxxx
Thank you Doug :-)

I need to create command buttons using VBA from the
'Control Box'. To be imbedded on a work***.
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 =
Active***.OLEObjects.Add(ClassType:="Forms.CommandButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75,
Width:=49.5,
Height:=33.75)
Set cbutton =
Active***.OLEObjects.Add(ClassType:="Forms.CommandButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=258, Top:=78.75, Width:=50.25,
Height:=27.75)

End Sub

hth,

Doug

"Jeff" <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:10842F1D-4319-490D-A730-A76044D14228@xxxxxxxxxxxxxxxx
Please help. :-)

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

thank you

--
Jeff












.