Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_SheetChange won't run
My understanding of Workbook_SheetChange is that with ANY change ANYWHERE in
the workbook, the code runs, correct? I can't get it to work and don't know why: GOAL: Resort the "PatientNamesRooms" (just a list of about 60 names and associated hospital room numbers) whenever a change is made on a different tab within the workbook. Cells in "PatientNamesRooms" are index formulas that get names based on room numbers from a main roster of names. We need to quickly find the name alphabetically to determine what room they're in. I've gotten the code to run (names to sort) using Worksheet SelectionChange. But that doesn't meet my need. It needs to resort anytime someone updates a name in the roster (like when a patient leaves or is admitted on the floor). CODE THAT WON'T RUN: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) Range("PatientNamesRooms").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End Sub Help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_SheetChange won't run
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_SheetChange won't run
Where have you got that code. Based on the format it shouwl be in
ThisWorkBook. Even then it is not going to work but it should be throwing an error. Try this... Right click the XL icon in the upper left corner of the screen next to File and select view code. Paste the following... Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False With Sheets("Sheet3") '***change me*** .Range("PatientNamesRooms").Sort Key1:=.Range("A2"), Order1:=xlAscending, _ Header:=xlYes End With ErrorHandler: MsgBox Err.Description Application.EnableEvents = True End Sub Note that you need to change the sheet reference to the sheet that the named range is on. That is because even though the named range is global code in the ThisWorkbook module acts on the active sheet which may not be the sheet you want sorted... Also I changed your sor a little bit to get rid of the xlGuess parameter. You should use xlYes or xlNo as they are less prone to mistakes. -- HTH... Jim Thomlinson "XL Novice DD..." wrote: My understanding of Workbook_SheetChange is that with ANY change ANYWHERE in the workbook, the code runs, correct? I can't get it to work and don't know why: GOAL: Resort the "PatientNamesRooms" (just a list of about 60 names and associated hospital room numbers) whenever a change is made on a different tab within the workbook. Cells in "PatientNamesRooms" are index formulas that get names based on room numbers from a main roster of names. We need to quickly find the name alphabetically to determine what room they're in. I've gotten the code to run (names to sort) using Worksheet SelectionChange. But that doesn't meet my need. It needs to resort anytime someone updates a name in the roster (like when a patient leaves or is admitted on the floor). CODE THAT WON'T RUN: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) Range("PatientNamesRooms").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End Sub Help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_SheetChange won't run
So you're sorting a single range on a (possibly) different worksheet no matter
where you make a change to any worksheet? If that's true, I'd specify the worksheet with the range named PatientNamesRooms and drop the .select stuff. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) with me.worksheets("somesheetnamehere").Range("PatientN amesRooms") .Sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal end with End Sub The only way I can think of where this is better than using the worksheet_activate event for that "somesheetnamehere" sheet is if you have multiple windows open and you're making changes in one window and want to see the sort reflected immediately on the other window. The worksheet_activate event would fire when the user selected the sheet. It sounds perfect to me. Or do you have a better reason (more curious than anything). XL Novice DD... wrote: My understanding of Workbook_SheetChange is that with ANY change ANYWHERE in the workbook, the code runs, correct? I can't get it to work and don't know why: GOAL: Resort the "PatientNamesRooms" (just a list of about 60 names and associated hospital room numbers) whenever a change is made on a different tab within the workbook. Cells in "PatientNamesRooms" are index formulas that get names based on room numbers from a main roster of names. We need to quickly find the name alphabetically to determine what room they're in. I've gotten the code to run (names to sort) using Worksheet SelectionChange. But that doesn't meet my need. It needs to resort anytime someone updates a name in the roster (like when a patient leaves or is admitted on the floor). CODE THAT WON'T RUN: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) Range("PatientNamesRooms").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End Sub Help? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_SheetChange won't run
Wow! Once I eliminate stupid little coding oversites (like inadvertantly
duplicating the 1st line when copying your code), this WORKED GREAT! And yes, I want this to re-alphabetize with ANY entry in the workbook, not just when this sheet is selected. Several other sheets need to adjust to the alphabetized list so when THEY'RE selected, they will always be current. And Thanks for clueing me in to that XL button. I never knew it was active. Always thought it decorative. Wow! DD... "Dave Peterson" wrote: So you're sorting a single range on a (possibly) different worksheet no matter where you make a change to any worksheet? If that's true, I'd specify the worksheet with the range named PatientNamesRooms and drop the .select stuff. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) with me.worksheets("somesheetnamehere").Range("PatientN amesRooms") .Sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal end with End Sub The only way I can think of where this is better than using the worksheet_activate event for that "somesheetnamehere" sheet is if you have multiple windows open and you're making changes in one window and want to see the sort reflected immediately on the other window. The worksheet_activate event would fire when the user selected the sheet. It sounds perfect to me. Or do you have a better reason (more curious than anything). XL Novice DD... wrote: My understanding of Workbook_SheetChange is that with ANY change ANYWHERE in the workbook, the code runs, correct? I can't get it to work and don't know why: GOAL: Resort the "PatientNamesRooms" (just a list of about 60 names and associated hospital room numbers) whenever a change is made on a different tab within the workbook. Cells in "PatientNamesRooms" are index formulas that get names based on room numbers from a main roster of names. We need to quickly find the name alphabetically to determine what room they're in. I've gotten the code to run (names to sort) using Worksheet SelectionChange. But that doesn't meet my need. It needs to resort anytime someone updates a name in the roster (like when a patient leaves or is admitted on the floor). CODE THAT WON'T RUN: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) Range("PatientNamesRooms").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal End Sub Help? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_SheetChange | Excel Programming | |||
Workbook_SheetChange not working | Excel Programming | |||
Workbook_SheetChange | Excel Programming | |||
workbook_sheetchange | Excel Programming | |||
Workbook_SheetChange will not fire | Excel Programming |