Link cell to workbook name

[YasserKhalil]

Hello
I want to put part of the workbook name to cell value

Say
Workbook name Test-01
cell E1 = the last letter of the workbook name so E1=1
Is it possible ?

[YasserKhalil]

I can use this formula

Please Login or Register  to view this content.

But I'm searching for UDF that can do that

[YasserKhalil]

I devised the following :

Please Login or Register  to view this content.

I need your suggestions about that!

[YasserKhalil]

I tested my function but when I change the workbook name and open it the value doesn't change at once . I have to press Alt+F9 to get the new results
I want to do it automatically

[Winon]

Hello YasserKhalil,

why don't you just try it like below.

Please Login or Register  to view this content.

[YasserKhalil]

Hello YasserKhalil,

why don't you just try it like below.

Please Login or Register  to view this content.

Thanks Mr. Winon
I don't want to put the code in Workbook_open .. as I don't feel satisfied with the codes related to this event..

And the code may be

Please Login or Register  to view this content.

[Winon]

Hello YasserKhalil,

Your code may work only if the Workbook Name is exactly seven characters long, otherwise it will fail.

If you don't want it in the Workbook Open event, you could use in a Macro.

Please Login or Register  to view this content.

[YasserKhalil]

Mr. Winon
Thank you for your great codes
Without testing your last code I'm sure it works very well but I will have to run the macro to get my result.I want that automatically as I have 20 workbooks with names like that (name-01) (name-02) and so on
I want to put the value in E1 so as to use it for many tasks!

It seems that I will use Workbook_open if I failed my UDF

[Winon]

Hi YasserKhalil,

I want that automatically as I have 20 workbooks ...

In that case when you get to Workbook (name-10), even my code will fail, so you might want to reconsider returning a value of 1 for Workbook one as "01". That will handle up to 99 Workbooks.

and the adjusted code for that with Workbook names of any length would be:

Please Login or Register  to view this content.

[YasserKhalil]

what about firing the UDF to get results without pressing Alt + F9
Is it possible or difficult somewhat.. please explain a little about that

[YasserKhalil]

what about firing the UDF to get results without pressing Alt + F9
Is it possible or difficult somewhat.. please explain a little about that

I hope to do that ..using vba

[YasserKhalil]

Please help me to do that..

[Winon]

Hello YasserKhalil,

The best way that I can think of to do this, is to use the Workbook Open Event.

Please Copy and Paste the Code below into your Workbook, and see if it works for you.

Please Login or Register  to view this content.

Regards.

[YasserKhalil]

Thank you very much Mr. Winon for your great help..

[Winon]

Hello YasserKhalil,

You are welcome, glad I could help, and I trust that you are satisfied with the solution.

Regards.