return tab names down a column with vba

[ammartino44]

Hello. How do I return all tab names down a column in master sheet. So, A1= Sheet 1 name, A2, Sheet 2 name, etc.

[daffodil11]

Please Login or Register  to view this content.

Also, this can be done without VBA strictly with formulas:
Tony Valko's Awesome Sheet List Formula

[wedzmer]

Please Login or Register  to view this content.

hi sir!

Does this code apply even if the worksheets' name is changed anytime during the process and the cells would dynamically adopt to these changes?

[daffodil11]

That VBA procedure would not, however it could be coded to that worksheet's specific coding to update whenever you select it.

[daffodil11]

Here is an example of the code you would apply to the master sheet to autoupdate:

Please Login or Register  to view this content.

[wedzmer]

i see... thanks for the info..
i'm really looking for ways as well on sheetname variations and i'm also having a very hard time since it's all VBA.

[daffodil11]

What do you mean with sheetname variations?

[wedzmer]

well, I'm wanting to achieve two scenarios actually...

1. that a sheetname would change depending on a certain cell result, and
2. that a cell from a master sheet would now adopt to the changes made on the sheetnames from the first condition.

[daffodil11]

A cell can dictate a sheet's name, sure.

That worksheet's code would be something like:

Please Login or Register  to view this content.

And when you select the master, it would rewrite all of the sheet names to stay up to date.

[wedzmer]

sorry sir, i'm quite confused..

does that code change the sheetname or change the cell name that copies the sheetname?

[daffodil11]

This code self-activates whenever the worksheet it's applied to changes the value of Cell A1.
As soon as you hit Enter, the worksheet name changes to the words in A1.


The previous code for Workbook_Activate would still apply, so that when you went back to the Master tab it would re-grab each worksheet name.

[daffodil11]

Here is an example for you to play with.

[wedzmer]

A cell can dictate a sheet's name, sure.

That worksheet's code would be something like:

Please Login or Register  to view this content.

And when you select the master, it would rewrite all of the sheet names to stay up to date.

I tried the code sir and unfortunately it didn't work.. the Private Sub Worksheet_Change is highlighted. I placed the code in between my other VBA codes.

It says:

Compile Error:
Ambiguous name detected: Worksheet_Change

[daffodil11]

Event code goes directly on the worksheet it applies to, not in a Module. Examine my example to see what goes where.

[wedzmer]

Event code goes directly on the worksheet it applies to, not in a Module. Examine my example to see what goes where.

kindly check on the codes sir:

Please Login or Register  to view this content.

[daffodil11]

You can't have two macros with the identical names, you'll need to combine the various Events.

Please Login or Register  to view this content.

[wedzmer]

You can't have two macros with the identical names, you'll need to combine the various Events.

Please Login or Register  to view this content.

Unfortunately it still isn't working on my excel file, maybe because the cell it's referring to ( $h$8 ) is a data validation drop down list from a table range.

[daffodil11]

Depends how you choose it. Are you typing or just picking from the list?

[wedzmer]

Depends how you choose it. Are you typing or just picking from the list?

picking from a list.. the $H$8 reference on the code is a dropdown list under data validation.

[daffodil11]

Are there any formulas on the worksheet that depend on H8?

[wedzmer]

yeah there are as well... the Percentile Scores changes everytime there is a change on the data chosen from the list on H8

[wedzmer]

would you mind taking a look at the sheet I'm working on?

ECR1.xlsm