Getting a button name

[Bob22110]

I have buttons (or shapes) on a worksheet each with different names. I need to get either the text on the button, or the name of the button when it is clicked. I name the button, button text, and the corresponding worksheet in a macro I use to create them. I want to then insert the name or the text on the button into a macro that will select a sheet also named the same as the button.

[mikerickson]

I'm not sure I understand your problem. You create the buttons, so you know their names.

[mikerickson]

This will change the name and caption of all the command buttons in a workbook to match the sheet on which they live. If there is more than one button on a sheet, an index will be added to the end of the button's name/caption.

Please Login or Register  to view this content.

[Bob22110]

I'm setting up a file where someone else will click a button titled add "Tech" (button) this macro will add a button to the worksheet, allow the user to name the button with an input box then and a new sheet with the tech's name on the sheet tab is added to the file.. There can be from 1 to 30 buttons on this “Main” sheet. Then, when the new button is clicked the button name will be handed to a generic macro that will select the corresponding sheet to enter data on this sheet. The file I'm writing, will have all the sheet tabs hidden the only visible sheet is the main sheet with the buttons. I don’t want to have to write a new macro each time I add a new tech. Get the button name when the button is clicked and use that name to tell the macro which sheet to select.

[colofnature]

Once you've created the button on your sheet, you can assign a macro to it using the Visual Basic for Applications Extensibility library. See http://www.cpearson.com/excel/vbe.htm for how.

Col

[Bob22110]

Thanks
I already know how to assign a macro to a button I created.

The problem is, I’m setting up a file to pass out at work that will only have one button on it. This button will be for the user to set the file up, to add more buttons based on how ever many people there are working in there department. There may be 1 to 20 additional buttons on the sheet. When the user clicks on the button to add more buttons, the user gives the button a name with an inputbox, a button with that name and a new worksheet with that name are created.

I can get the button name with this code.

ActiveSheet.Shapes("Button 1").Select

ThisName = Selection.Name

For this to work, the macro has to go back and select that button. I will not know which button was clicked. I need the button name to be passed to the macro when it is clicked. I want to write one macro that will get the name of the button the user clicked, and then go to that corresponding worksheet.

If this file was just for me, I would add a button then write code for that button to go to the worksheet with that button name. I’m trying to set a file up where another user can add new buttons as needed, and not have to send the file back to me to add more code to go to that sheet.

[VBA Noob]

Wrap your code or post will be deleted

VBA Noob

[colofnature]

If the initial button is called "AddButton" then the code to do this would look like:

Please Login or Register  to view this content.

This adds a button below AddButton, or whichever button is at the bottom, adds the sheet and assigns a Click event to the new button.


Col

[Bob22110]

Thanks
I guess I just don't know how to get my point acroos. I've already got a button on my worksheet that adds new buttons to the worksheet. The problem I'm having is once the new button that has been added is clicked. I need the name of the new button or the text from the new button to be passed the the macro that is assigned to the new button so the macro will know witch worksheet to select and to add data to. When the new button is created by the macro assigned to the "Add New Button" button that is already on the work sheet is clicked a worksheet is also created with tha same name.

I short, there is already one button on the sheet that when clicked, adds new buttons. This new button is named by the user with an input box and a work sheet is also created with the sheet tab named the same as the new button. Once the new button that has just been added by the user is clicked, I need the name of that new button to be passsed to an existing macro that will now to select the sheet in the workbook with the same name as the new button that was clicked.

I guess this just can't be done.

[Leith Ross]

Hello Bob22110,

There are 2 types of controls that can be placed on a worksheet. From your description, it sounds like you are using controls from the "Forms" toolbar. You can use the code below to return the name of the control or shape that called the macro.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Bob22110]

That is exactly what I needed. Just two lines of code, I knew it had to be easy.