Excel Remove Message and auto fill the year

Copper Contributor

I have two questions I need to get answered:

1. When I open my Excel Database I get the following Message: "This workbook contains links to one or more external sources that could be unsafe." I get this because I have cells that I copied from one worksheet to another. 

WHAT I WOULD LIKE TO KNOW: Is there a way perhaps with VBA code that I can get this message to not show up every time I open the database. If the answer is yes You need to know I am not that good at writing code so I will need that sent to me in detail. If there is no way to get that message to disappear that will be ok I can live with that.

2. I need to auto fill when ever the database is open the following:

The current year  (for example: 2022)

The next year  (for example: 2023)

and one cell that shows the current month and year.     (for example: May, 2022)

I have tried looking on line and none of the code or formulas that I have looked at work.

Any help you can give will be greatly appreciated.

Thanks

Bill

18 Replies

@Delaney1382bill 

2) Select the cell in which you want the current year.

Enter the formula =YEAR(TODAY())

Select the cell in which you want next year.

Enter the formula =YEAR(TODAY())+1

Select the cell in which you want the current month and year.

Enter the formula =TEXT(TODAY(),"mmmm, yyyy")

That worked perfect thank you so much for your help.

I take it there is no way to get rid of that message that keeps popping up since there is nothing addressing that issue. I can live with that though

@Delaney1382bill

Do you want to keep the links, but without the warning, or would you like to get rid of the links?

I want to keep the links as each one links to another report I just want to get rid of the warning if that is possible. Also I will need to know where the code would go and what the code would be as I am not that good at doing VBA

@Delaney1382bill 

Select File > Info.

On the right hand side, you should see 'Edit Links to Files'.

Click on this link.

In the 'Edit Links' dialog, click 'Startup Prompt...'

Choose the second or third option, according to your preference.

Click OK.

S1460.png

I don't have "Edit Links to Files" I tried to do a copy paste to show you what opens but it did not work.
Here is some of what is on the right side of the page
Status - categories -hyperlink Base - Related Documents - tags - template
on the left side there are 2 boxes that are highlighted yellow:
Security Information - active content might contain malware and other security hazards. The active content in this file is allowed to run because it is a Trusted Document, and policies or trust center setting aren't restricting it.
Trust center Settings
Learn more about active content
Learn more about trusted documents

I don't know if I need to go into one of the topics above.
Please advise

@Delaney1382bill 

I'm afraid I cannot help you without seeing (a copy of) the workbook. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

I must have been over tired and looked in a worksheet that didn't have the copy paste with the link. I came in this morning and checked all of the files and found the "Edit Links to Files" and made the changes you showed me.
I will see if that works today I have several more reports to create with that Link.
Thanks for all of your help and again sorry about the mistake.
Bill

@Delaney1382bill 

I am doing the copy paste (using: Paste Link (N). After I complete the paste I protect the sheet. Under the protection options I have none of the boxes checked. I don't know if I am suppose to have something checked. 

When I go back in and reopen the worksheet I get two warnings (I have attached them for your review). I would like to know how I can get rid of these two warnings like you helped me with earlier. I tried to look in the "Edit Links to files" (I have the last one checked "don't show the warning but do the update".

Is there anything I can do to get rid of the warning signs. Once I click ok the link still works the way it is supposed to.

@Delaney1382bill

Select the link to the file that cannot be found and click Break Link.

Can you give me some idea where I might find the link that I need to break and how I break the link.
Also do I have to do that with each sheet that has the link
Sorry to be such a bother and thanks again for all your help

@Delaney1382bill 

Select File > Info.

On the right hand side, click Edit Links to Files.

You should see the link or links here, and the Break Link button.

Ok here is the problem I went into the Edit Links to files as you said but the problem is: The Break Link button is grayed out and no matter what I do I can't delete the 2 files in the source window.
Actually the only 2 buttons that are not grayed out are "Open Source" and "Check Status". In the start up prompt: I have "Don't Display the Alert and update Links". No matter what prompt I select I cannot get Brake Lint to be active. Also I can't select the file in the source window (left or right mouse won't do anything with it)
Could you walk me through what I need to do. If I can get rid of those 2 warnings that pop up I will be done with this project.
Thanks in advance.
Bill

@Delaney1382bill 

Without seeing (a copy of) the workbook, I don't know what to do about the Break Link problem.

But see what happens if you select "Don't display the alert and don't update automatic links"

@Hans Vogelaar 

I have attached the file for your review. 

@Delaney1382bill 

I was able to get into the "Break link" but when I did break the link the copy past disappeared.
I did find on the left side of the Info page in the section "Security Information" and trust center settings there could be something to remove the warning's I just couldn't figure out which section it would be in. If you could look in there and find the correct page and what I need to check that should resolve the problem.
Thanks

@Delaney1382bill 

The reason that you can't break the links is that the worksheet is protected. Do you know the password to unprotect the sheet? If so, please unprotect it, then try to break the links again.

@Delaney1382bill 

Did you try setting "Don't display the alert and don't update automatic links"?