create command buttons using vba

[Guest]

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

 

[Doug Glancy]

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.CommandButton.1",
Link:=False _
, DisplayAsIcon:=False, Left:=173.25, Top:=75.75, Width:=49.5,
Height:=33.75)
Set cbutton = ActiveSheet.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

 

[Guest]

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

 

[Doug Glancy]

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

 

[Guest]

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

 

[Doug Glancy]

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

 

[Guest]

Yes this works great
thank you

All I have to do now is change the command button names each time through
the loop

I believe it should be... forms.CommandButton.i

Hmmmm...
Maybe it will work using the for loop var i


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

 

[Guest]

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.

 

[Guest]

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

 

[Doug Glancy]

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 ActiveSheet.Cells(i + ((i - 1) * 7), 2)
Set cbutton =
ActiveSheet.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

 

[Guest]

Thank you Doug
--
Jeff

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 ActiveSheet.Cells(i + ((i - 1) * 7), 2)
Set cbutton =
ActiveSheet.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