Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Code
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Code
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 On Dec 19, 11:01 am, Kerry wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Code
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Code
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. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet Code
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 "Kerry" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the code for the active worksheet? | Excel Discussion (Misc queries) | |||
VBA Code for selecting worksheet | Excel Discussion (Misc queries) | |||
Need Worksheet Code | Excel Worksheet Functions | |||
worksheet code | Excel Discussion (Misc queries) | |||
Code to determine name of Worksheet | Excel Discussion (Misc queries) |