VBA that works in Excel 2010 but not Excel 2016

[anasttin]

Hi,

Can someone advise me on a workaround for this issue?

This snippet of VBA code below works fine in Excel 2010, but crashes with no debug button in Excel 2016.

If I explicitly state the workbook to Activate, it works fine, but 2016 doesn't seem to like the workbook name as a variable. Is there a way to code around this without explicitly stating the workbook name to activate?

Thanks

=========================
mycurrsup = Range("mycurrsup")
Windows(mycurrsup).Activate
=========================

[leelnich]

So mycurrsup is a Named Range (single cell) containing the name of a workbook as text, correct? What is the error message?

[anasttin]

Yes "mycurrsup" is a named range single cell containing the name of a workbook as text and there is no problem with this code in Excel 2010.

I'm a bit confused now though, as I couldn't recall the error message so went to test it on someone's laptop that uses Excel 2016 and it didn't fail during that test.

Certainly, I avoided the error on the first person's laptop by explicitly stating the name of the workbook in the VBA code and I know this does happen, but it appears it doesn't happen on EVERYONE'S Excel 2016.

Maybe there's a setting in Excel 2016 that is causing the problem???

[Norie]

What's the name of the workbook you are trying to activate?

Are you using the full workbook name including the file extension?

PS Why are you using Windows(...) and now Workbooks(...)?

[leelnich]

I see you didn't fully qualify the Range reference. Make sure the right worksheet/workbook is active or you won't get the right cell.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[anasttin]

What's the name of the workbook you are trying to activate?

Are you using the full workbook name including the file extension?

PS Why are you using Windows(...) and now Workbooks(...)?

The name of the workbook is "Cattle 2017 v3.xlsm" (obviously without the quotes).

Yes, I'm using the full filename with extension.

Windows(...) vs Workbooks(...) - what is the difference? I've always used "Windows". Is there a distinction, and if so, what is it? Should I be using it?

[anasttin]

I see you didn't fully qualify the Range reference. Make sure the right worksheet/workbook is active or you won't get the right cell.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Hi Leelnich.

What does "fully qualify the Range reference" mean?

Is it something new to Excel 2016?

Thanks

[leelnich]

No, I just meant reference a specific workbook and/or worksheet so VBA doesn't assume you mean whatever Worksheet is currently active.

Please Login or Register  to view this content.

This might not be necessary, depending on how you defined mycurrsup (or the correct sheet is currently active.)