2

I have seen several questions relating to Object variable not set when using Activeworkbook, but none for my particular scenario. I use some code in workbook_open that tests a condition to decide if the user should be shown a form

If Len(ActiveWorkbook.Names("DCStype_Selected").RefersToRange.Value) = 0 Then

This works fine for most users 99% of the time. However some users have reported error 91 - Object Variable or with block variable not set, and it is on the activeworkbook line.
I believe I have tracked the scenario where this happens and it is always for users who do not have my excel workbook in a trusted location, and so are prompted to enable macros and this error occurs when they click the "enable" button.
Once I have talked them through setting a folder as a trusted location, and moving the workbook to that folder, this issue does not occur.

  1. Could anyone tell me a more defensive way to code my line, so that it works regardless of a users security settings?

  2. Could some one explain why this error only occurs in this specific scenario?

Community
  • 1
  • 1
Mark Moore
  • 520
  • 4
  • 13
  • Not sure, but this may help. Instead of `ActiveWorkbook`, assign the workbook to a variable when opening it, eg, `Set wB = Workbooks.Open(Filename:=Path & Filename, ReadOnly:=True)`, then test for wB. See my answer to a previous post [here](http://stackoverflow.com/questions/24191731/excel-macro-to-combine-workbooks-runtime-error-1004/24195994#24195994) – DaveU Feb 26 '15 at 03:14
  • @DaveU - Will explore this, but its not possible with that exact syntax. This workbook is used by multiple customers across multiple locations, none of which are actually in our company, so I have no control over hardware, software, file locations etc. So how to retrieve details of the current workbook without using "activeworkbook" seems to be my problem – Mark Moore Feb 26 '15 at 09:39
  • 1
    Oh, I see. Maybe another way would be to loop thru all the open workbooks and set wB to the workbook named "DCStype_Selected". Then test wB and take appropriate action if wB = Nothing. – DaveU Feb 26 '15 at 16:19
  • @DaveU - Thanks Dave, that sounds a good option – Mark Moore Feb 26 '15 at 16:47

2 Answers2

0

I would set ActiveWorkbook.Names("DCStype_Selected").RefersToRange.Value as a variable and on error

  • either set a message box to tell them to move the file to a trusted folder

  • or set the variable to 0 to avert further damage and debug mode

    Sub ert() On Error GoTo Err
    ... 'your code
    If False Then
    Err:
    ... 'MsgBox or setting the var to 0 End If
    End Sub

user3819867
  • 1,114
  • 1
  • 8
  • 18
  • Thanks, but this isn't really suitable. I have extensive error handling already across all my modules, the process of explaining how to set trusted locations is not suitable for a message box. I need the workbook object, so setting to 0 is not an option. Think DaveU's comments is the way to go – Mark Moore Feb 26 '15 at 17:33
0

A little late to the party but I had the exact same problem... eventually got around it like this:

Dim wBook As Workbook

Do While wBook Is Nothing
    Set wBook = ActiveWorkbook
    DoEvents
    ' Prob. put a counter in here to prevent infinite looping...
Loop
SlowLearner
  • 3,086
  • 24
  • 54