Sheet Name VBA

[Presly]

hy all,
i'm got VBA code for taking a sheetname into cell, but problem is it's not run automaticaly, so everytime i change the sheetname the i must recalculated to make the sheetname on cell change.
this is the code :

Public Function sheetname(ByVal target As Range)
sheetname = target.Worksheet.Name
End Function

whit that code i can take the sheetname with this formula =sheetname(A1), please help me to make this run automatic
i'm newbie to VBA

 

[FormR]

Welcome to the forum.

You can use the following formula that will update in real time. Note that the file must be saved.

=TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"]",REPT(" ",255)),255))

 

[FormR]

Or if you insist on the UDF maybe try this:

Public Function sheetname(ByVal target As Range)
Application.Volatile
sheetname = target.Worksheet.Name
End Function

 

[Presly]

it works really fine.
thanks a a lot

 

[DavidCoop]

Try Adding Application.Volatile as the first line of your Function.

Sorry! I didn't see that same response already posted.