Could I please request assistance with this VBA 91 error?

dincigneri

New Member
Joined
Jun 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Whole query:
VBA Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
VBA Code:
Range("G6").Select
to
VBA Code:
Application.Goto Sheet13.Range("G6")
 
Upvote 0
You shouldn't need to actually 'select' anything to do this ...
Further to that, you could try this code.

VBA 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
 
Upvote 0
Solution
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.

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

Thanks again Peter! Have a lovely night.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
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.

VBA Code:
.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
 
Upvote 0
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.

VBA Code:
.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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top