Auto update calculation upon opening file

In the attached file i have a simple macro and formula to give me the filename in a cell. If I copy the file and rename it, when I open it it still has the old filename. However, if I click into the formula and hit enter it will update it to the proper name. I do have the auto update option checked off in the options, so I'm not sure why it isn't updating this cell. It's important for me to be able to copy a template file, rename it, and have it automatically use the new filename in the cell.

Can anyone help me make this work?

To see what I mean open the attached file and you will see the cell with the filename in it. Then copy the file and rename it. Open the new file and it will still have the old filename. Click into the formula and hit enter and you will see the cell value change.

Thanks

Originally Posted by Richard Buttrey

Hi,

That behaviour is because there is no change in the workbook and specifically the cell with the UDF =File()

You could add the following in the workbook open event to force this change...

Please Login or Register  to view this content.

but why bother with a UDF anyway, just use the following compound formula which uses standard excel functions

Please Login or Register  to view this content.

Thanks, I will try your macro code when I'm back at work.

I actually already use the CELL formula for the attendance sheet that I made. Each sheet refers back to the previous week to pull forward some carry forward numbers. It is working, but once people open more than 1 file at a time it messes things up. I'm trying to replace it with a macro that allows multiple sheets to be open at once without interfering with each other.

I do have a question though, since my VBA experience is limited. Would I add that sub code of yours below the code that I already use? Or is there a different place to put it?

Thanks.

Originally Posted by Richard Buttrey

Hi,

There's no reason why the Excel formula should mess things up. It will always return the name of the workbook in which it is placed.

Re you last question. The answer is No. The point about your function code not updating is precisely because it is in effect a 'normal' excel function albeit a UDF function. This function does not rely on any other cell and hence will never change unless you either edit it and enter it as you have found, or get the system to do the same thing. That's what my VBA instruction does. You need to place it in the Workbook_Open event.

Click on the workbook name in the Visual Basic environment, select the 'Workbook' option from the left hand drop down and the Open event from the right hand drop down.

HTH

On the system I use opening a new workbook makes the old workbook display the new workbook name because as far as I understand it displays the active workbook.

But regardless, I don't quite unerstand your instructions on where I would put that code. I don't really know my way around the VBA environment yet. If all of the boxes are closed and the visual basic editor is blank, where would I go in the menu to get to the part that you are talking about?

Thank you very much for taking your time.

Originally Posted by Richard Buttrey

Hi,

Your original post talked of saving the test2 file with a new name, (say test 3), then re-opening the test 3 file and expecting to see the new file name, i.e. test 3.

Therefore you need to find the Workbook Open event in test2 as I described in my last post and put the code in there. Obviously when you save test2 as test3, test3 will also contain the same workbook open event and will update the file name for you when you next open test3.

Rgds

Please excuse my ignorance. It was the description in your last post that I did not follow (the one you are alluding to now). You said to "Click on the workbook name in the Visual Basic environment, select the 'Workbook' option from the left hand drop down and the Open event from the right hand drop down." I'm lost on the first part because I'm not sure where to click on the workbook name. I don't see it anywhere where it can be clicked on. Sorry.

If I may jump in, this is not working for me.
I am facing the same problem.

What I am doing is the following:
I am looking for the header-value of the first column with a value. The function is working, and pasted below:

Please Login or Register  to view this content.

this is working fine. I call this function in each row (making sort of a gantt chart) as followed:

Please Login or Register  to view this content.

This is working perfect. Except: if I change the columns with the value, the cell doesn't update.
I've tried several things, the solution above with

Please Login or Register  to view this content.

also tried

Please Login or Register  to view this content.

Nothing helped. I need to be IN the cell, give a RETURN at the end, and only than the cell is updated???

any suggestions?

Aha,

Somebody pointed my to a solution.
I already tried Application.Volatile, however, I used this on load of the workbook. You have to add this code at the top of your UDF!

Now it is working fine!

Thank you, your description makes sense and I was able to add that in. It still doesn't seem to work though. I attached a copy of the file and you will see when you open it that D13 still says "Test2" instead of "Copy of Test2".

Does the code for the openworkbook sub need to be more specific than "D13"? I tried changing it to "Sheet1'!D13" and it still didn't work. Maybe there is a more specific way of defining it that you are aware of.

Thanks again.

anyone with any ideas?

monday morning and I'm back at work. bump