Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cell change
Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I
am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#2
|
|||
|
|||
Put all of your code that you want to run into a macro called something. I
assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#3
|
|||
|
|||
Otto
Thanks wirked a treat, now I just have to figure out how to do the script without turning to the Data worksheet...thanks again "Otto Moehrbach" wrote: Put all of your code that you want to run into a macro called something. I assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#4
|
|||
|
|||
What?
"CMCCONNA" wrote in message ... Otto Thanks wirked a treat, now I just have to figure out how to do the script without turning to the Data worksheet...thanks again "Otto Moehrbach" wrote: Put all of your code that you want to run into a macro called something. I assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#5
|
|||
|
|||
Otto
Thanks for your help, it worked excellently.... I am however in the process of building a workbook that has a data sheet which calculates out all my information and a summary sheet which the user can input data onto. When the user inputs data ont the summary sheet, it runs the sort query below on the Data sheet... As you can see the way it is set up below it causes the data sheet to flash infront of the summary sheet for a second ......simple problem I bet.... I guess it has something to do with the Application.Goto statement...... So i am currently working on it... Thanks for your help "Otto Moehrbach" wrote: What? "CMCCONNA" wrote in message ... Otto Thanks wirked a treat, now I just have to figure out how to do the script without turning to the Data worksheet...thanks again "Otto Moehrbach" wrote: Put all of your code that you want to run into a macro called something. I assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#6
|
|||
|
|||
I don't know what you want to do, but you can freeze the screen to keep the
"flash" down. Freezing the screen does not interfere with anything the code is doing, it only keeps a constant image on the screen. To freeze the screen use: Application.ScreenUpdating - False When you are ready to go back to normal use the same statement with a True instead of a False. HTH Otto "CMCCONNA" wrote in message ... Otto Thanks for your help, it worked excellently.... I am however in the process of building a workbook that has a data sheet which calculates out all my information and a summary sheet which the user can input data onto. When the user inputs data ont the summary sheet, it runs the sort query below on the Data sheet... As you can see the way it is set up below it causes the data sheet to flash infront of the summary sheet for a second ......simple problem I bet.... I guess it has something to do with the Application.Goto statement...... So i am currently working on it... Thanks for your help "Otto Moehrbach" wrote: What? "CMCCONNA" wrote in message ... Otto Thanks wirked a treat, now I just have to figure out how to do the script without turning to the Data worksheet...thanks again "Otto Moehrbach" wrote: Put all of your code that you want to run into a macro called something. I assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#7
|
|||
|
|||
That dash should be an equal sign, as in:
Application.ScreenUpdating = False HTH Otto "Otto Moehrbach" wrote in message ... I don't know what you want to do, but you can freeze the screen to keep the "flash" down. Freezing the screen does not interfere with anything the code is doing, it only keeps a constant image on the screen. To freeze the screen use: Application.ScreenUpdating - False When you are ready to go back to normal use the same statement with a True instead of a False. HTH Otto "CMCCONNA" wrote in message ... Otto Thanks for your help, it worked excellently.... I am however in the process of building a workbook that has a data sheet which calculates out all my information and a summary sheet which the user can input data onto. When the user inputs data ont the summary sheet, it runs the sort query below on the Data sheet... As you can see the way it is set up below it causes the data sheet to flash infront of the summary sheet for a second ......simple problem I bet.... I guess it has something to do with the Application.Goto statement...... So i am currently working on it... Thanks for your help "Otto Moehrbach" wrote: What? "CMCCONNA" wrote in message ... Otto Thanks wirked a treat, now I just have to figure out how to do the script without turning to the Data worksheet...thanks again "Otto Moehrbach" wrote: Put all of your code that you want to run into a macro called something. I assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
#8
|
|||
|
|||
Otto
Thats exaclty what I was looking for.Thanks "Otto Moehrbach" wrote: That dash should be an equal sign, as in: Application.ScreenUpdating = False HTH Otto "Otto Moehrbach" wrote in message ... I don't know what you want to do, but you can freeze the screen to keep the "flash" down. Freezing the screen does not interfere with anything the code is doing, it only keeps a constant image on the screen. To freeze the screen use: Application.ScreenUpdating - False When you are ready to go back to normal use the same statement with a True instead of a False. HTH Otto "CMCCONNA" wrote in message ... Otto Thanks for your help, it worked excellently.... I am however in the process of building a workbook that has a data sheet which calculates out all my information and a summary sheet which the user can input data onto. When the user inputs data ont the summary sheet, it runs the sort query below on the Data sheet... As you can see the way it is set up below it causes the data sheet to flash infront of the summary sheet for a second ......simple problem I bet.... I guess it has something to do with the Application.Goto statement...... So i am currently working on it... Thanks for your help "Otto Moehrbach" wrote: What? "CMCCONNA" wrote in message ... Otto Thanks wirked a treat, now I just have to figure out how to do the script without turning to the Data worksheet...thanks again "Otto Moehrbach" wrote: Put all of your code that you want to run into a macro called something. I assumed a name of MyMacro. Put that macro into a normal module. Then paste the following macro into the sheet module of the sheet that contains the range named TABLECALC. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("TABLECALC")) Is Nothing Then _ Call MyMacro End Sub MyMacro will run anytime TABLECALC is changed. If your code changes the value in TABLECALC and you don't want the event macro to fire when that happens, then you need to bracket that portion of your code that changes TABLECALC with: Application.EnableEvents = False 'The code that changes TABLECALC Application.EnableEvents = True HTH Otto "CMCCONNA" wrote in message ... Hi all, I need help...... I have this macro that run when I hit Ctrl +A. I am trying to get it to run when the cell contents of a range called "TABLECALC" are changed Any suggestions would be great. I have looked at the worksheet change event but cant seem to get it to work. Thanks Sheets("data").Visible = True Application.Goto Reference:="tablesort" Selection.Sort Key1:=Range("J65"), Order1:=xlDescending, Key2:=Range( _ "I65"), Order2:=xlAscending, Key3:=Range("B65"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal ActiveWindow.SelectedSheets.Visible = False Range("A1").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell color change based on due date | New Users to Excel | |||
cell color index comparison | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Look for change next blank cell in Range | Excel Worksheet Functions |