VBA to put workbook name & sheet name in cell M1

[mikeburg]

I need help coming up with VBA that will put the Sheet's file name & a dash with the sheet name in cell M1 when the sheet's name is changed (right click sheeted tab & rename).

For example:

When changing tab named Sheet2 to 08-39 for the workbook TaxWorkSheets.xls,

put in cell M1:

TaxWorkSheets.xls-08-39

I really appreciate you help. mikeburg

[royUK]

You don't need VBA for the file pathuse the CELL Function.
=CELL("filename") will return the file name & full path

or

Please Login or Register  to view this content.

=

[mikeburg]

I do not want the drive & path included. Instead, I need the workbook name with the sheet tab name separated by a hyphen. In other words with this VBA, I am getting:

F:\data\MSExcel - 08-39

When I want:

TaxWorkSheets.xls-08-39, TaxWorkSheets.xls is the workbook name & 08-39 is the sheet tab's name.

Any other ideas?

Thanks a million, mikeburg

[Leith Ross]

Hello mikeburg,

Change Roy's code to this...

Please Login or Register  to view this content.

I need help coming up with VBA that will put the Sheet's file name & a dash with the sheet name in cell M1 when the sheet's name is changed (right click sheeted tab & rename).

Unfortunately, there are no events triggered when the Sheet is renamed.

[mikeburg]

Works great!. Thanks so very much. mikeburg