Could I please request assistance with this VBA 91 error?

[dincigneri]

Hi all, I was wondering if anyone can help me jump this last hurdle in my first VBA project? As a complete newbie I have made good progress browsing this site and watching videos however cannot get this final bug fixed.

I have a workbook where all sheets are protected. On sheet13 however, is a table from an external data source that needs to be refreshed on when opening the file.

I have created a macro to unlock, refresh and lock (with password) that sheet only, and I have created another sub to trigger this macro when opening the file. I recorded the macro and then edited it - I typed in the password into the code as the password was not being recorded, and I believe I also changed 'ActiveSheet' to 'Sheet13' as I want to make sure the right sheet is updated (not knowing which 'activesheet' a user may open this shared file on). This worked for a single test on Friday arvo and I went to the weekend quite satisfied...

However today I and other users receive a '91' error on opening the file. In turn the macro doesn't run automatically. It will then execute fine when I hit F5 - which makes my testing very tricky - why is it only failing when opening the file (more to the point, when i send it to another user to test it for me - it always fails!).

The line of code that is the issue is marked below. Searching online, the fix for this error has led me on a path of defining and setting variables in my code, though I am not sure I need to do that? Will very much appreciate any insight anyone can provide here. Thanks


Error line:

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Whole query:

Sub Unlock_Refresh_Lock()

Sheet13.Unprotect "password"

Range("G6").Select

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Sheet13.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _

, AllowUsingPivotTables:=True

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

You shouldn't need to actually 'select' anything to do this but as a 'quick-fix' to test, try changing

Range("G6").Select

to

Application.Goto Sheet13.Range("G6")

 

[Peter_SSs]

You shouldn't need to actually 'select' anything to do this ...

Further to that, you could try this code.

Sub Unlock_Refresh_Lock_v2()
  With Sheet13
    .Unprotect "password"
    .Range("G6").ListObject.QueryTable.Refresh BackgroundQuery:=False
    .Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True
  End With
End Sub

 

[dincigneri]

Peter, thank you so much for your fast response. Yes this code works perfectly, thankyou!

I did get onto the WITH statement this morning thinking I could use it to solve my '91' error, but could not work out the syntax in the middle line to direct that specific table to refresh. Looking at your code I was a long way off, but you've given me something to study and try to understand.

.Range("G6").ListObject.QueryTable.Refresh BackgroundQuery:=False

Thanks again Peter! Have a lovely night.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[dincigneri]

You're welcome. Thanks for the follow-up.

Good morning Peter. Sadly I sent out the excel document to team members to test last night and they all advise they are receiving the '91' error, with this line being the culprit.

.Range("G6").ListObject.QueryTable.Refresh BackgroundQuery:=False

The '91' error will not occur for me no matter how I try, even emailing the file to myself, however when I email the file to another user it always fails. One user mentioned 'it asked me to verify my credentials then didn't like my user' before it failed, so I will investigate what that means.

I will continue to search for clues but just wanted to update. I'm not sure the error is with the VBA code but possibly other permissions? Thanks for your help

 

[dincigneri]

Good morning Peter. Sadly I sent out the excel document to team members to test last night and they all advise they are receiving the '91' error, with this line being the culprit.

.Range("G6").ListObject.QueryTable.Refresh BackgroundQuery:=False

The '91' error will not occur for me no matter how I try, even emailing the file to myself, however when I email the file to another user it always fails. One user mentioned 'it asked me to verify my credentials then didn't like my user' before it failed, so I will investigate what that means.

I will continue to search for clues but just wanted to update. I'm not sure the error is with the VBA code but possibly other permissions? Thanks for your help

Just to update, it seems my error related to the external data connection being a Sharepoint list, and the recipients weren't part of the Sharepoint Team so were failing to authenticate the content. It's a test Team with only myself in it so makes sense. Added the users and they report no errors (which confuses me why they said they had a 91 error when it likely wasn't the case!). Thanks

 

[Peter_SSs]

Thanks for the update.