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

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

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

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

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

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

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

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