Adding a shared network drive as a Trusted Location

1) Open start menu and look for Internet Options
2) Go to Security
3) Trusted Sites
4) Sites
5) Untick Require server verification (https for all sites in this zone
6) Add the drive (you can add just the mapped drive letter, and it will convert it to UNC, or just the ip of the host)

In excel if it doesn't work also check that in the Trust Center and make sure network locations are authorized and that macro security level is set to at least have a notification that you will be able to accept.