VBA - Run time error '9': subscript out of range

LPieter

New Member
Joined
Oct 1, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everybody

I'm trying to write a macro to enable colleagues to sort a table on a protected worksheet. This table and worksheet are protected in regards to formatting to prevent unwanted changes to the tabel by colleagues who aren't very excel-savvy. Unfortunately, because of the locked formatting, it's impossible to sort the table columns as you wish without removing the protection.
To solve this problem, I'd like to add a clickable button above the table that sorts the table by running a macro. I think I got the macro, but keep on getting an error. I have no idea what the problem is and would love your advice.
Many thanks, Pieter

VBA Code:
Sub sort_table()

Application.ScreenUpdating = False

activesheet.Unprotect Password:="Formules"

Dim ws As Worksheet
Dim iTable As ListObject
Dim wsname As String
Dim Tname As String

Set ws = activesheet
wsname = activesheet.Name
Tname = "T" & wsname

Set iTable = ws.ListObjects("Tname").Range

With iTable.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("D3"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("B3"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With

activesheet.Protect Password:="Formules", DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowSorting:=True, AllowFiltering:=True

Application.ScreenUpdating = 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
Probably you got this error in setting iTable. And probably you have in your worksheet an listobject (Excel Table) named after sheet name, but with added T at front.
If it is like that, then:
Try content (not name - in doble quotes) of Tname variable:
VBA Code:
Set iTable = ws.ListObjects(Tname).Range
 
Upvote 0
Probably you got this error in setting iTable. And probably you have in your worksheet an listobject (Excel Table) named after sheet name, but with added T at front.
If it is like that, then:
Try content (not name - in doble quotes) of Tname variable:
VBA Code:
Set iTable = ws.ListObjects(Tname).Range
Thank you for your reply. I already tried this as the debug interface highlights this line:
VBA Code:
Set iTable = ws.ListObjects(Tname).Range
. The same error appears after running the macro without the quotes around Tname.
 
Upvote 0
That code should give you a type mismatch since iTable is a ListObject not a Range. It should be:

Code:
Set iTable = ws.ListObjects(Tname)

If that still gives a subscript out of range, then the table name is wrong. If it's the only table on the worksheet, you can use:

Code:
Set iTable = ws.ListObjects(1)
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,620
Members
452,786
Latest member
k3calloway

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