command buttons and macros

[Ody]

Hoping to get some direction on the following:

My Set Up:
Home page is a summary page of all sheets in my workbook. All other sheets are hidden. Every time a user adds a new sheet to my work book (via macro) the summary page updates (via macro) with certain information from that newly added sheet.

What I would like to have happen is for every new sheet added, a command button also gets added to the summary page, on the next available row in the summary table, that would then take the user to that new sheet.

I have some code for adding command buttons but I have 2 problmes with it:

ActiveSheet.Buttons.Add(button coordinates).OnAction = "macro name"

1. I'm not sure how to make it dynamic so that a button gets added to the next row in my summary table. In this case it would be in cell A everytime it's added.

2. How do I get excel to reference the name of the sheet that was just added by my user so it can then assign a "go to sheet" macro to the newly added command button?


Thanks for your time and assistance!

 

[ColinKJ]

Hi Ody,

I don't have a direct answer to your enquiry, but, instead of using active x command buttons, have you considered using a name in a cell activated by an appropriate code in "selection change".

All do-able programatically as sheets are added.

 

[Ody]

If what you're referring to is what I'm thinking of, then that might work, too.

However, I'd have no clue where to begin with implementing that? What would some of the VBA look like, with in the context of my problem in the original post?

 

[ColinKJ]

I'll put together an example WB and get back to you

 

[Ody]

Very much appreciated, thanks!

 

[ColinKJ]

Hi Ody,

I've put together an example WB named Add Sheets which you can download from:

http://www.box.net/shared/shu7tt4xyi

When you click on the Add New Sheet button, you are asked to enter a name for the new sheet.

If the sheet name already exists you are asked to enter a different name.

The new sheet is added, hidden, and the name is added to the list on the Summary sheet.

If you click on one of the sheet names on the Summary Sheet, the selected sheet is displayed.

When you return to the Summary Sheet, all other sheets are hidden again.

 

[Ody]

Appreciate the effort, greatly! I'll have a look later on and provide some feedback. Thanks again mate!