Cell change

[Guest]

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 Moehrbach]

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

 

[Guest]

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]

What?

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

 

[Guest]

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]

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

 

[Otto Moehrbach]

That dash should be an equal sign, as in:
Application.ScreenUpdating = False
HTH Otto

 

[Guest]

Otto

Thats exaclty what I was looking for.Thanks