AutoSaveOn issue with Excel 2013 and 2016

ThomasFoster

New Member
Joined
Jul 18, 2019
Messages
1
Sorry if this is in the wrong location, this is the first time I am posting on this forum.

My issue is a regarding a "Compile Error: Method or data member not found" that happens when an excel file is opened by some users. These users have either excel 2013 or 2016 installed.

The compile error is related to the "ThisWorkbook.AutoSaveOn" command in the below code that runs in the WorkBook_Open() Sub.
Code:
If Val(Application.Version) > 15 Then        If ThisWorkbook.AutoSaveOn Then ThisWorkbook.AutoSaveOn = False
    End If

I have googled this issue and found references to this not working well with Excel 2013 but nothing regarding issues with Excel 2016.
It is also not even going to my error handler function it is failing right away.

I am at a bit of a loss as to how to fix this in such a way that lets the file work on Excel 2013/2016 and yet still disables the autosave when running in later versions where the code works no problem.

Is this something that can be fixed or do I need to rip out this section for it to work on older versions.

Thanks for your help.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I ran into this issue recently - the problem is that you are directly accessing the ThisWorkbook module. Older versions of Excel don't like it much for some reason.

Dim and Set a Workbook variable to ThisWorkbook, then use that in your IF block to access the AutoSaveOn property:

Dim wbThis As Workbook
Set wbThis = ThisWorkbook

VBA Code:
If Val(Application.Version) > 15 Then
    If wbThis.AutoSaveOn Then wbThis.AutoSaveOn = False
End If

Hope that does the trick.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
In 2013 and 2016 there is no AutoSaveOn property for a Workbook, so the error will be a compile error, not a runtime one.
 

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
In 2013 and 2016 there is no AutoSaveOn property for a Workbook, so the error will be a compile error, not a runtime one.
You're right, but by setting the a Workbook variable wbThis to ThisWorkbook, it prevents the compile error that you would get by directly trying to access a non-existent property with ThisWorkbook.AutoSaveOn. My code compiles in Excel 2010, then I handle the runtime error with an OnError statement.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

then I handle the runtime error with an OnError statement.
Ah, OK. That will work, but the Val(Application.Version) test above won't, since 2016+ is version 16.
 

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hmmm, are you sure? I just repeated the OP's code for the version check, but my equivalent code is checking for
VBA Code:
Val(Application.Version) > 14
and seems to work well. I thought the AutoSaveOn property of the Workbook object was introduced in Excel 2013 (Application.Version = 15), could be wrong though.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Nope. I tested in 2016 and the property doesn't exist, so you get a 438 error.
 

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Nope. I tested in 2016 and the property doesn't exist, so you get a 438 error.
OK, worth knowing. I've looked around online a few times previously to try to find out when this property came into existence, couldn't find anything definitive. Good job I'm using OnError in conjunction with the version check and setting a Workbook object to avoid the compile error. Should probably increase by Application.Version test number to > 15 though by the sounds of it.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you've got an On Error handler for it, you don't really need the other test (which is flawed anyway). ;)
 

vincehardwick

New Member
Joined
Feb 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
If you've got an On Error handler for it, you don't really need the other test (which is flawed anyway). ;)
Yeah, the more I read about this, the less relevant Application.Version becomes after Excel 2016.

This page suggests an alternative method of programmatically determining the version of Excel you're running, but by the sounds of the comment section underneath the article, it's not watertight...

Check the application version in Modern Office - The Excelguru Blog
 

Forum statistics

Threads
1,136,257
Messages
5,674,665
Members
419,521
Latest member
Jennifer4Dillon

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
Top