CODEWITHSUNDEEP
vbaexcel
mits
mits

Reputation: 926

VBA excel - Activate method fails when file opens from a network folder

I have an excel file with several macros. I want to get some things done when the file opens. I use the following code:

Private Sub Workbook_Open()
    Worksheets(1).Activate
    pass = "mits"
    Worksheets(1).Range("Q2:S16").NumberFormat = "General"
    Call prostasiaON(True)
End Sub

This code is written in 'This_Workbook module', pass is a public variable declared in module 1 and prostasiaON(mode as Boolean) is a Sub that does some other things.

When my file is in a local drive it opens as it should. If I copy my file in a network drive and try to open it, it throws a

Run-time error '1004': Method Activate of class Worksheet failed.

in the second line. Can anybody please explain why is this happening and how can I avoid this?

Edit: before throwing this error, excel asks me if I want to allow edit of this file and I choose yes.

Upvotes: 0

Views: 1140

Answers (2)

mits
mits

Reputation: 926

Here 's what solved the problem. I 'm posting this as a reference for future seekers.

In this post I got a solution that could match: https://social.technet.microsoft.com/Forums/ie/en-US/f4c993e6-e226-4e39-8964-8f3a4b3dcf21/protected-view-for-networked-drives?forum=officeitproprevious

The sence is similar to Variantus's answer above.

If you know for sure that the network drive that your file is into is indeed a trusted location, here 's the steps of the solution:

  1. open Ιnternet Εxplorer, click Τools/Internet Options and select the tab Security.
  2. I selected Local Intranet because that 's the case.
  3. hit Sites/Advanced.
  4. in the first field write the path of the network drive or folder that your file is into (and you want to set is as a secure location).
  5. hit the Add button and you 're ready.

Thank you all for your time.

Upvotes: 0

Variatus
Variatus

Reputation: 14383

The problem is related to the question whether you want to allow editing because the code won't actually run until you have agreed although the workbook appears loaded. You won't have the problem if you can set trust and permissions so as to avoid the incomplete opening of the workbook and the question whether you want to allow editing. If you are unable to avoid this question you will also be unable to avoid the error that occurs on Activate. So, the thing I do is to add On Error Resume Next and live without Worksheets(1) being activated under the circumstances you described.

Upvotes: 1

Related Questions