Re: create command buttons using vba
- From: Jeff <Jeff@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 6 Dec 2006 08:28:00 -0800
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 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
"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 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
"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 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" <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 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.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
"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 worksheet
using VBA. The number of command buttons created will vary.
The command button type is from the 'Control Tool box'
thank you
--
Jeff
- Prev by Date: Re: Select page tab when form opens
- Next by Date: Re: Excel in winform freezes Excel
- Previous by thread: conditional concatenation
- Next by thread: AutoFilter.Filters--What column is being referenced when On?
- Index(es):
Relevant Pages
|
