SheetName (Application.Caller.Parent.Name) Forces Excel to Save File

[Mike McCollister]

I have a VBA function in Excel that I have been using for years that will
display the sheet name in a cell. The VBA function is as follows:

Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function

This works great. However, I have found that if I am using this function if
I open a worksheet and not change anything Excel will prompt me if I want to
save the worksheet if I have not made any changes. Why is this happening and
is there a way to prevent this from happening?

Thanks,

Mike

 

[Guest]

The problem may be Application.Volitile

If the function re-calculates, Excel may suspect that the worksheet has
changed.

 

[Bob Phillips]

You could just use a worksheet function

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Bob Phillips]

It will always recalculate upon opening, so it is definitely the
Application.Volatile.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Dave Peterson]

Won't this suffer the same volatility problem?

You could just use a worksheet function

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I have a VBA function in Excel that I have been using for years that will
display the sheet name in a cell. The VBA function is as follows:

Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function

This works great. However, I have found that if I am using this function if
I open a worksheet and not change anything Excel will prompt me if I want to
save the worksheet if I have not made any changes. Why is this happening and
is there a way to prevent this from happening?

Thanks,

Mike

 

[Mike McCollister]

Dang. If I remove that applicaiton.volatile it is not updating. I guess that
I will stay with the function.

Mike

 

[Mike McCollister]

I'll consider that. It is just not as friendly as the SheetName function
that I have written up.

Mike

You could just use a worksheet function

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I have a VBA function in Excel that I have been using for years that will
display the sheet name in a cell. The VBA function is as follows:

Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function

This works great. However, I have found that if I am using this function if
I open a worksheet and not change anything Excel will prompt me if I want to
save the worksheet if I have not made any changes. Why is this happening and
is there a way to prevent this from happening?

Thanks,

Mike