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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
If the Table is on a different sheet you would not activate but assuming that the Table is on Sheet 3 use
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)
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)
ASKER
Also, the (1) needs to be there or you get a data mismatch error.