getting a cell value to show the workbook file name

timwe

New Member
Joined
Dec 22, 2016
Messages
12
Hi,

I would like to make a cell in my workbook show the name of the workbook.

The sheet where I want it to show is called INPUT SHEET and the cell is "AY2"

I have tried the following:

fname = ActiveWorkbook.Name
Range("AY2").Value = Left(fname, InStr(1, fname, ".xls") - 1)

This code is inserted in This Workbook under Private Sub workbook_open.
The code works but I keep getting a runtime error when opening the workbook that points to second line above. IT says "Application-defined or object defined error"

Anyone know what could be causing this? should I place the code somewhere else?

Br
timwe
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You need something like this:

Modify to your needs:

Code:
Sub Workbook_Name()
Cells(1, 1).Value = ThisWorkbook.Name
End Sub
 
Upvote 0
Thanks for the answer.

I assume the Cells(1, 1) refers to cell A1?

If I want this code to run when the workbook is opened or closed, should it be placed where i had it or where should this go?

br
 
Upvote 0
I figured you knew how to modify my post:

Try this:

Code:
Private Sub Workbook_Open()
Range("AY2").Value = ThisWorkbook.Name
End Sub
 
Upvote 0
Thanks.

This code works, although I now get the xlsm ending of the file name vs before which isn't necessary but not an issue.

One more thing: when I lock the worksheet and workbook and then reopen the file, there is an automatic popup "unprotect sheet" with a request for the password. How do I get rid of that?

BR

Tim
 
Upvote 0
Actually, now I am getting the same error as earlier, where the popup is not there but instead a runtime error which refers back to the code.
 
Upvote 0
If you lock the workbook that means you want no changes made until you unlock the workbook.

And there would be no purpose in locking the workbook then unlocking it just to enter this workbook name and then relocking it. I'm not sure why you need the script to keep entering the workbook name every time the workbook is opened.
 
Upvote 0
In your original post you never mentioned having the workbook locked.

I'm not sure why your getting an error code. I'm not familiar with what happens when you attempt to modify sheets when the workbook is locked.
 
Upvote 0
The excel workbook is a template that needs to be filled in by a number of users. I have locked the cells that are not to be changed. I also locked the workbook structure so that the tab name cannot be changed. The issue is that when I open the template it prompts for a password. If I click "cancel", it refers to the code above.

I figured it was the original code that had some issue, but the one you proposed also causes the same error. not sure what to do. mayhbe I should unlock the specific cell that the workbook name is inputted into.
 
Last edited:
Upvote 0
If you want the workbook name entered into a cell that cell would need to be unlocked.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top