Can anybody tell me why this code will not run in XL97? It falls over at the myarray declaration. (Run-time error 438. Object does not support this property or method). The code runs happily in XL2003.
It appears not to like the use of Sheet code names. With some tweaking I can get it to run with the Sheet names but, since I can not guarantee these will not change, I would prefer to use Sheet code names. I have not been able to find anything that indicates that Excel 97 handles Sheet code names differently to Excel 2003.
Unfortunately the client is using various versions of Excel otherwise I would stop the file opening in Excel 97.
Public Sub RemoveFilter()
Dim myarray As Variant
Dim n As Variant
Dim sh As Worksheet
myarray = Array(Sheet2, Sheet5, Sheet6, Sheet7, Sheet8, Sheet12)
For Each n In myarray
Set sh = n
sh.Unprotect
If sh.FilterMode Then
sh.ShowAllData
End If
Next n
End Sub
Any help gratefully received.
Replies (4)
Please login or register to join the discussion.
Will this work?
Public Sub RemoveFilter()
Dim myarray As String
Dim n As Variant
Dim sh As Worksheet
myarray = Array(Sheet2.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet12.Name)
For Each n In myarray
Set sh = Worksheets(n)
sh.Unprotect
If sh.FilterMode Then
sh.ShowAllData
End If
Next n
End Sub
Somewhat surprised...
That the suggested code worked since it defined myarray as a string and yet assigned a variant array (of strings) to the myarray variable (which would thus result in a type mismatch). I presume that you kept your declaration statement of
Dim myarray As Variant
myarray = Array(Sheet2.Name, Sheet3.Name, Sheet4.Name) 'etc
Richard