Worksheet Code

[Kerry]

I have an excel spreadsheet 2003 which I use to sort information. On the
index sheet I have buttons with macros that hide certain columns and then
sort information on the remaining column in sheet 2. If a user doesn't unsort
when they return to the index sheet this creates a problem the next time
someone sorts using a button.
I have inserted an unsort code into the buttons so that it will unsort first
and unhide the columns then run the sort macro which works really well.
Someone suggested I put the unsort code into the index sheet code which I
have but it doesn't work.

Private Sub Worksheet_Activate()

Sheets("Contact Numbers").Select
Selection.AutoFilter Field:=3
Columns("D:S").Select
Selection.EntireColumn.Hidden = False
Sheets("Index").Select
Range("A1").Select
End Sub

The error starts at the Columns("D:S").Select with the message
Select method of range class failed

Can someone suggest a solution to this please.

Thankyou Kerry

 

[carlo]

Hey Kerry

Try this:

Private Sub Worksheet_Activate()

with Sheets("Contact Numbers")
.AutoFilter Field:=3
.Columns("D:S").EntireColumn.Hidden = False
Range("A1").Select
End Sub

should work....selecting and deselecting is not useful because it
takes too much time. (unless you want to show the user, what excel is
doing)

hth

Carlo

 

[Jim Cone]

Kerry,
Unqualified ranges in a sheet module refer to the sheet containing the module.
Also, since you select another sheet and then select the index sheet again,
the code repeats itself and repeats itself and on and on.
Use something like this...
'--
Private Sub Worksheet_Activate()
On Error GoTo WentBad
Application.EnableEvents = False
Sheets("Contact Numbers").Select
Selection.AutoFilter Field:=3
Sheets("Contact Numbers").Columns("D:S").Select
Selection.EntireColumn.Hidden = False
Sheets("Index").Select
Sheets("Index").Range("A1").Select
WentBad:
Application.EnableEvents = True
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)
-direct downloads from the website coming in a couple of days-


"Kerry"wrote in message
I have an excel spreadsheet 2003 which I use to sort information. On the
index sheet I have buttons with macros that hide certain columns and then
sort information on the remaining column in sheet 2. If a user doesn't unsort
when they return to the index sheet this creates a problem the next time
someone sorts using a button.
I have inserted an unsort code into the buttons so that it will unsort first
and unhide the columns then run the sort macro which works really well.
Someone suggested I put the unsort code into the index sheet code which I
have but it doesn't work.

Private Sub Worksheet_Activate()

Sheets("Contact Numbers").Select
Selection.AutoFilter Field:=3
Columns("D:S").Select
Selection.EntireColumn.Hidden = False
Sheets("Index").Select
Range("A1").Select
End Sub

The error starts at the Columns("D:S").Select with the message
Select method of range class failed

Can someone suggest a solution to this please.

Thankyou Kerry

 

[Earl Kiosterud]

Kerry,

This line:
Selection.AutoFilter Field:=3
will be reliable only if you know for certain what cell will be selected. The selected cell
will remain selected, even when the workbook has been saved and reopened. You select A1 in
your last line, but it could have been changed in the meantime. It would be better to use
something like:
Range("A1").AutoFilter Field:=3

I'm not sure what you mean by "Unsort," but it shouldn't matter what order your sheet's in
when someone wants to sort it later. It's typical to sort on any column for any particular
need, then sort on another column for another need, at will.

 

[Kerry]

Thanks to Carlo, Jim and Earl for your help,
The code that Carlo suggested resulted in the error message "expected end
with" as I am not a vb expert I wasn't sure where to go from there.
Jim's code works a treat and has solved the problem. I have added all the
Selection.Autofilter Fields:=1
Selection.Autofilter Fields:=2 etc so that it unsorts any column that has
been sorted and then unhides them so thanks for that.
Thanks to Earl for your support as well.

Kerry