Link to home
Start Free TrialLog in
Avatar of kwarden13
kwarden13

asked on

VBA Works in Excel 2010 Not 2016 Help!

I am using Excel 2010, however, my users are using Excel 2013 or Excel 2016. My code is getting a run time error stating subscript out of range on the bolded last line. Any ideas???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strLastDataCol As String
Dim lngTargetCol As Long
Dim tbl As ListObject

Application.EnableEvents = True
Application.ScreenUpdating = False

Call UnProtectAll

[b]strLastDataCol = Split(Cells(1, ActiveSheet.ListObjects("SpendingTotals").ListColumns.Count).Address, "$")(1)[/b]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike in IT
Mike in IT
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kwarden13
kwarden13

ASKER

it appears to only be happening to windows 10 users. Windows 8 is okay with Excel 2013.  Any ideas???

Also, the (1) needs to be there or you get a data mismatch error.
Inserting the code to activate the sheet fixed the issue. Thank you!
Glad that helped.

I didn't think that the (1)  was the problem, I was just curious. I've not run into a place where I've had that.
Avatar of Roy Cox
Sheet event code works on the sheet that contains the code so activesheet is not needed, nor would you need to activate the sheet. If the code is referring to a different sheet then you would specify that sheet, not activate it.

This code being in the  event code of a sheet wiill automatically work on the active sheet, so you do not need ActiveSheet. You can use Me. . I believe I amended this code for you in a previous question.

strLastDataCol = Split(Cells(1, Me.ListObjects("SpendingTotals").ListColumns.Count).Address, "$")(1)

Open in new window


If the Table is on a different sheet you would not activate but assuming that the Table is on Sheet 3 use

strLastDataCol = Split(Cells(1, Sheet3.ListObjects("SpendingTotals").ListColumns.Count).Address, "$")(1)

Open in new window