Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
command buttons
Hi, I have a large number of command buttons and I need to change the name on
them, they all have the same name, I know that I can change the name by using edit but I was wondering if there is a quick way to do it with more than one at a time, I have about 300 buttons in two rows down two columns. Thanks in advance, Wildauk. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
command buttons
Name or caption. (I assume caption)
each has their own property window. you can do it with code if there is some pattern to your madness Dim oleObj as Object Dim cbtn as MSforms.Commandbutton Dim sRoot as String, i as Long sRoot = "BaseName" i = 1 for each oleobj in Activesheet.OleObject if typeof oleObj.Object is MSForms.CommandButton then set cbtn = oleObj.Object cbtn.Caption = sRoot & i 'cbtn.name = sRoot & i i = i + 1 end if next If you change the name (not the caption), any event code will need to be changed to reflect the new name. -- regards, Tom Ogilvy "wildauk" wrote in message ... Hi, I have a large number of command buttons and I need to change the name on them, they all have the same name, I know that I can change the name by using edit but I was wondering if there is a quick way to do it with more than one at a time, I have about 300 buttons in two rows down two columns. Thanks in advance, Wildauk. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
command buttons
Thanks Tom, excuse my ignorance but where would I put the code.
Wildauk. "Tom Ogilvy" wrote: Name or caption. (I assume caption) each has their own property window. you can do it with code if there is some pattern to your madness Dim oleObj as Object Dim cbtn as MSforms.Commandbutton Dim sRoot as String, i as Long sRoot = "BaseName" i = 1 for each oleobj in Activesheet.OleObject if typeof oleObj.Object is MSForms.CommandButton then set cbtn = oleObj.Object cbtn.Caption = sRoot & i 'cbtn.name = sRoot & i i = i + 1 end if next If you change the name (not the caption), any event code will need to be changed to reflect the new name. -- regards, Tom Ogilvy "wildauk" wrote in message ... Hi, I have a large number of command buttons and I need to change the name on them, they all have the same name, I know that I can change the name by using edit but I was wondering if there is a quick way to do it with more than one at a time, I have about 300 buttons in two rows down two columns. Thanks in advance, Wildauk. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
command buttons
sub RenameBoxes()
Dim oleObj as Object Dim cbtn as MSforms.Commandbutton Dim sRoot as String, i as Long sRoot = "BaseName" i = 1 for each oleobj in Activesheet.OleObjects if typeof oleObj.Object is MSForms.CommandButton then set cbtn = oleObj.Object cbtn.Caption = sRoot & i 'cbtn.name = sRoot & i i = i + 1 end if next End Sub Save the workbook. Alt+F11 to get to the VBE then Insert=Module paste the code in there Alt+F11 to get back to Excel Tools=macro=macros, select RenameBoxes (sheet with boxes shoud be the active sheet.) Hit the Run button. If it screws things up, close the workbook without saving. -- Regards, Tom Ogilvy "wildauk" wrote in message ... Thanks Tom, excuse my ignorance but where would I put the code. Wildauk. "Tom Ogilvy" wrote: Name or caption. (I assume caption) each has their own property window. you can do it with code if there is some pattern to your madness Dim oleObj as Object Dim cbtn as MSforms.Commandbutton Dim sRoot as String, i as Long sRoot = "BaseName" i = 1 for each oleobj in Activesheet.OleObject if typeof oleObj.Object is MSForms.CommandButton then set cbtn = oleObj.Object cbtn.Caption = sRoot & i 'cbtn.name = sRoot & i i = i + 1 end if next If you change the name (not the caption), any event code will need to be changed to reflect the new name. -- regards, Tom Ogilvy "wildauk" wrote in message ... Hi, I have a large number of command buttons and I need to change the name on them, they all have the same name, I know that I can change the name by using edit but I was wondering if there is a quick way to do it with more than one at a time, I have about 300 buttons in two rows down two columns. Thanks in advance, Wildauk. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Buttons | Excel Programming | |||
command buttons | Excel Worksheet Functions | |||
Command buttons | Excel Programming | |||
Control Buttons vs. Command Buttons | Excel Programming | |||
Command buttons not available | Excel Programming |