| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Command Buttons | Excel Discussion (Misc queries) | |||
| Command buttons | Excel Discussion (Misc queries) | |||
| Command Buttons | Excel Discussion (Misc queries) | |||
| Create a search Field within a worksheet to search command buttons | Excel Programming | |||
| Control Buttons vs. Command Buttons | Excel Programming | |||
Threaded Mode
