Link Parts Of Workbook Filename To Cells

Define a name called FileName (via the Name manager on the Formulas tab) using the formula:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

then in your cells use:
=TRIM(left(FileName, SEARCH("REV", Filename)-1))
and
=MID(Filename, search("REV",filename)+3,255)
respectively.

Hey, this looks hopeful! Thanks so much! Here's some issues though:

Cell B11:C11 shows the extension of the file name too - '08.xlsm' where it should only show '08'.

Could these formulas be moved into the macro for this sheet? I'd like the option to be there for someone to change the values in those cells and be warned that it doesn't match the file name. And is there a way to keep them from displaying anything if the filename is exactly this: 'Blank Inspection Reports & C of Cs.xlsm'?

Which macro are you talking about specifically?

I'm still not clear. When do you want these cells populated (i.e. what do you want to trigger the code that will populate them) and what exactly do you want to happen if the code is triggered and the cells already have other values in them? Also, do I understand that you want the code to monitor those cells for changes too? Should the user be allowed to override the values you have specified?

Alright, let me try to be clearer!

This is the way I'd like it to work:
The user opens the 'Blank Inspection Reports & C of Cs' workbook. The Data Entry Page is empty. The workbook gets saved as a part name and rev, such as '123456-7 REV08'. Now, Cell B9:G9 displays '123456-7' and Cell B11:C11 displays '08'. (The ideal time for those two cells to display their information would be right after the user enters a value in the cells above them, actually.) If this workbook gets saved as another part number, then the numbers in those cells should immediately reflect the new filename, replacing whatever was in them before.

And the user shouldn't be able to change the values to anything but the filename, but I'd like the cells to be 'editable' - which is why I thought the code should be on the VBA sheet rather than in the actual cells.. For example, sometimes a Rev will change, and I'd like the user to be able to change the value in Cell B11:C11, and upon hitting Enter/etc. he should get an error message stating that the filename should be changed in order to change that value, and the rev should switch back to what it was.

If I'm still not clear enough on anything, just give me a yell.. and thanks so much!

Oh! One more thing - it would be good if the user would get an error message if he tries to enter information before saving the workbook as a part number and rev first...

Originally Posted by Juda

And the user shouldn't be able to change the values to anything but the filename, but I'd like the cells to be 'editable' - which is why I thought the code should be on the VBA sheet rather than in the actual cells.. For example, sometimes a Rev will change, and I'd like the user to be able to change the value in Cell B11:C11, and upon hitting Enter/etc. he should get an error message stating that the filename should be changed in order to change that value, and the rev should switch back to what it was.

I don't see why you would want those cells to be editable if you won't let them be changed?!

It seems to me that the simplest thing would be to trap the save event and have the cells populated automatically then?

Can you test this for the save code? It's a bit rough but should work, I think. Once it's OK I'll add in the other check.

For the file check when entering data, add this to the start of the worksheet_Change code for the data sheet:

Please Login or Register  to view this content.

That code needs to go after the:

Please Login or Register  to view this content.

line in the worksheet's code module (right-click the sheet tab and choose 'View Code')

Another addition to the Change Event - this goes above the last snippet:

Please Login or Register  to view this content.

Can you post the current version?

Add this to the ThisWorkbook module:

Please Login or Register  to view this content.

That's odd - it was working in my tests. I won't be able to look at it until next week now, I'm afraid.

Unfortunately, it's at work and I'm on my way home...

I seem to have somehow missed a bit when posting. It should be:

Please Login or Register  to view this content.