Excel VBA Method 'Range' of object' _Global' Failed only when opening the document

Question

Hi All

I don't write allot of VBA script so there may be a simple answer for this.

I have a script that defines a Range. EG Range("D2:D5000"). The script is set to run on Opening of the document or on Cell changes. This works without an issue if I have the document open or open the document directly. We use a third party app to open the document which opens it in Protected Mode and you need to click Enable Edits.

When you open the document using the Third party app, and after you click enable edits you get the error. Method columns of object_Global Failed. If you stop the script and run it again it works without issue.

If you know why this would only be an issue when opening the document in Protect mode or a solution to get around this, please let me know.

I am assuming it's something to do with not having sheet focus but don't really know.

Thanks for your time in Advance.

Craig

Answer 1

We do not see a code, but that error based on no use references to worksheet.
That simple to fix if you have access to it.

like:

myColumn = Cells(1, Columns.Count).End(xlToLeft).Column   

to:

dim wks as worksheet: set wks = activesheet 'or other'  
myColumn = Wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column  

Regards

Answer 2

Hi OSkar,

Thanks for your time.

Unfortunately, the solution you suggested did not work, still get the same error.

Here is the line of Code I have problems with.

Set InstructedDate = Range("D2:D5000")

Regards
Craig

Answer 3

Hi,

So I took your code a little further and add dim the workbook as well

Dim WB As Workbook: Set WB = ThisWorkbook  
Dim wks As Worksheet: Set wks = WB.Sheets("Sheet1")  

This looks to have fix my problem.

Thanks

Craig