Attach code

[MAX]

How to attach the code to a Sheet_Change event

 

[Sheeloo]

Right-click on the sheet name at the bottom
Choose View Code to open VB Editor
Click on the first dropdown and choose Worksheet
You can see all related events in the second dropdown...
Choose Change

You will see
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Enter the code between Sub/End Sub.

 

[MAX]

One must have a macro to attach a code?
Thanks in advance.

 

[Sheeloo]

Not sure what you mean...

You must have some code to attach to the event... otherwise it won't do any
thing...

If you already have a macro say calculate_something then you can simple
write calculate_something between Sub/End Sub like this

Private Sub Worksheet_Change(ByVal Target As Range)
calculate_something
End Sub

 

[Shane Devenshire]

Hi,

Not sure either, what you mean, but what Sheeloo is saying is that you put
your own code inside the Sheet_Change procedure, here is an example:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub

This code is setup to respond to changes in cell A1 of the sheet in which
the code is attached. You can change that range to anything you want.
Whatever you want Excel to do when the change occurs you add to the line that
reads 'Your code here.

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.

Why don't you give us more details, like what you want it to do and what
change your are interested in detecting and in which cells.

 

[MAX]

Hi Shane,
Yesterday you post this to me (below) and I created a table with a range of
"A1:I6".
In row 1 starting from A1 I have these titles:
Team Play Win Draw Lose GF GA GD Points

In column A starting from A2 I have These names:
Roma
Milan
Lazio
Bari
Siena

The range I want to sort is "A2:I6".
I also attached the code you gave me, but when I post a result and for
example the points increase, a window is coming up with Compile error and
Syntax error. Why?

Thanks.


Regaring posting the whole file - I don't know what works best for that,
others here at the site could tell you.

There is no built-in feature to sort your data automatically. However, if
you want the sort to occur whenever you change or enter data in the important
columns -

1. First record the steps necessary to do the sort on you data. Then attach
the code to a Sheet_Change event.

Here is an example:

the range F2:I6 is my sample range where if an entry is changed the sort
reoccurs automatically. F2 because row 1 is titles and F is the first column
what something relevant could change. I6 you will need to adjust to include
as many rows as you want to trigger the auto sort. I2, F2 and H2 are the
Points, GF and GD columns. "A1:I6" is the entire sort range including the
titles. Again you will need to adjust that to fit your situation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("F2:I6"))
If Not isect Is Nothing Then
With ActiveSheet
With .Sort.SortFields
.Clear
.Add Key:=Range("I2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("H2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("F2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
End With
With .Sort
.SetRange Range("A1:I6")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End Sub