create command buttons using vba
 

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

create command buttons using vba
 

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

create command buttons using vba
 

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

create command buttons using vba
 

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

create command buttons using vba
 

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

create command buttons using vba
 

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

create command buttons using vba
 

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.Comma ndButton.i", _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
End With
Next i




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

create command buttons using vba
 

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

create command buttons using vba
 

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

create command buttons using vba
 

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.Comma ndButton.1", _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
cbutton.Name = "Cmd_Select" & i
End With
Next i

End Sub

Happy Thanksgiving,

Doug


"Jeff" wrote in message
...
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 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

create command buttons using vba
 

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 ActiveSheet.Cells(i + ((i - 1) * 7), 2)
Set cbutton =
ActiveSheet.OLEObjects.Add(ClassType:="forms.Comma ndButton.1", _
Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
cbutton.Name = "Cmd_Select" & i
End With
Next i

End Sub

Happy Thanksgiving,

Doug


"Jeff" wrote in message
...
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 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