i know that must have a very obvious solution but,
how do i use two same macros in a sheet?
ex. i use Worksheet_Change

and later Worksheet_Change again
but excel says "repeated name found: Worksheet_Change"

i would like to use Worksheet_Change twice with different properties
how would i do it

--
Message posted from http://www.ExcelForum.com

Leandrocg,

You can't repeat it the same procedure.

Try putting conditional logic into your code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
'Something with A1
ElseIf Target = Range("B1") Then
'something with B1
Else
'something else
End If
End Sub

Rob


"leandrocg " wrote in message
...
i know that must have a very obvious solution but,
how do i use two same macros in a sheet?
ex. i use Worksheet_Change

and later Worksheet_Change again
but excel says "repeated name found: Worksheet_Change"

i would like to use Worksheet_Change twice with different properties,
how would i do it?


---
Message posted from http://www.ExcelForum.com/

sorry but let me be more specific

i am using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("N9:V16").Sort Key1:=Range("V9:V16"), Order1:=xlDescending
Key2:=Range("P9"), Order2:=xlDescending, Key3:=Range("U9")
Order3:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

wich is an auto sort macro

how do i keep this auto sort range and i add another auto sort range
like Range("N20:V27")

Leandr

--
Message posted from http://www.ExcelForum.com

Leandro,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("N9:V16").Sort Key1:=Range("V9:V16"), Order1:=xlDescending,
Key2:=Range("P9"), Order2:=xlDescending, Key3:=Range("U9"),
Order3:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("N20:V27").Sort Key1:=Range("V20:V27"), Order1:=xlDescending,
Key2:=Range("P20"), Order2:=xlDescending, Key3:=Range("U20"),
Order3:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub


Rob


"leandrocg " wrote in message
...
sorry but let me be more specific

i am using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("N9:V16").Sort Key1:=Range("V9:V16"), Order1:=xlDescending,
Key2:=Range("P9"), Order2:=xlDescending, Key3:=Range("U9"),
Order3:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

wich is an auto sort macro

how do i keep this auto sort range and i add another auto sort range?
like Range("N20:V27")

Leandro


---
Message posted from http://www.ExcelForum.com/

thx a lot guy....
i had tried that before but it didnt work...


anyway :)
thx i've been working on that table for days


---
Message posted from http://www.ExcelForum.com/

Please describe your real goal? Perhaps there's an answer that you're not
aware of.


"leandrocg " wrote in message
...
thx a lot guy....
i had tried that before but it didnt work...


anyway :)
thx i've been working on that table for days


---
Message posted from http://www.ExcelForum.com/

this might be easier to change if your ranges change:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng1Key As Range
Dim Rng2Key As Range

If Target.Cells.Count 1 Then Exit Sub

Set Rng1 = Me.Range("n9:v16")
Set Rng2 = Me.Range("n20:v27")

'just gonna check one of the key columns.
'if you change anything else, it won't affect the sort
Set Rng1Key = Me.Range("v9:v16,p9:p16,u9:u16")
Set Rng2Key = Me.Range("v20:v27,p20:p27,u20:u27")

On Error GoTo errHandler:

If Not (Intersect(Target, Rng1Key) Is Nothing) Then
'it's in one of the keys in rng1
Application.EnableEvents = False
Rng1.Sort key1:=Me.Range("v9"), order1:=xlDescending, _
key2:=Me.Range("p9"), order2:=xlDescending, _
key3:=Me.Range("u9"), order3:=xlDescending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

ElseIf Not (Intersect(Target, Rng2Key) Is Nothing) Then
Rng2.Sort key1:=Me.Range("v9"), order1:=xlDescending, _
key2:=Me.Range("p9"), order2:=xlDescending, _
key3:=Me.Range("u9"), order3:=xlDescending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If

errHandler:
Application.EnableEvents = True

End Sub

=====
But sorts are very forgiving. You can tell excel that the key is A1 and it'll
use all of column A.

And if the keys are the same (V,P,U), then you could condense the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng1Key As Range
Dim Rng2Key As Range
Dim SortRng As Range

If Target.Cells.Count 1 Then Exit Sub

Set Rng1 = Me.Range("n9:v16")
Set Rng2 = Me.Range("n20:v27")

'just gonna check one of the key columns.
'if you change anything else, it won't affect the sort
Set Rng1Key = Me.Range("v9:v16,p9:p16,u9:u16")
Set Rng2Key = Me.Range("v20:v27,p20:p27,u20:u27")

On Error GoTo errHandler:

If Intersect(Target, Application.Union(Rng1Key, Rng2Key)) Is Nothing Then
Exit Sub
End If

If Intersect(Target, Rng1Key) Is Nothing Then
Set SortRng = Rng2
Else
Set SortRng = Rng1
End If

Application.EnableEvents = False
SortRng.Sort key1:=Me.Range("v1"), order1:=xlDescending, _
key2:=Me.Range("p1"), order2:=xlDescending, _
key3:=Me.Range("u1"), order3:=xlDescending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

errHandler:
Application.EnableEvents = True

End Sub

====
And the application.enableevents seems to be overkill. The worksheet_change
event didn't fire with a sort.

"leandrocg <" wrote:

sorry but let me be more specific

i am using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Range("N9:V16").Sort Key1:=Range("V9:V16"), Order1:=xlDescending,
Key2:=Range("P9"), Order2:=xlDescending, Key3:=Range("U9"),
Order3:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

wich is an auto sort macro

how do i keep this auto sort range and i add another auto sort range?
like Range("N20:V27")

Leandro

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Just a word of warning on Rob's sample code.

He's checking the value of the target against the value in A1 and then in B1.
If you really wanted to check to see if A1 or B1 were changing, you could change
his code slightly:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = Range("A1").address Then
'Something with A1
ElseIf Target.address = Range("B1").address Then
'something with B1
Else
'something else
End If
End Sub

Rob van Gelder wrote:

Leandrocg,

You can't repeat it the same procedure.

Try putting conditional logic into your code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
'Something with A1
ElseIf Target = Range("B1") Then
'something with B1
Else
'something else
End If
End Sub

Rob

"leandrocg " wrote in message
...
i know that must have a very obvious solution but,
how do i use two same macros in a sheet?
ex. i use Worksheet_Change

and later Worksheet_Change again
but excel says "repeated name found: Worksheet_Change"

i would like to use Worksheet_Change twice with different properties,
how would i do it?


---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson

You're absolutely correct - thank you for pointing that out.
That will teach me for not testing first.

Rob

"Dave Peterson" wrote in message
...
Just a word of warning on Rob's sample code.

He's checking the value of the target against the value in A1 and then in
B1.
If you really wanted to check to see if A1 or B1 were changing, you could
change
his code slightly:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = Range("A1").address Then
'Something with A1
ElseIf Target.address = Range("B1").address Then
'something with B1
Else
'something else
End If
End Sub

Rob van Gelder wrote:

Leandrocg,

You can't repeat it the same procedure.

Try putting conditional logic into your code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
'Something with A1
ElseIf Target = Range("B1") Then
'something with B1
Else
'something else
End If
End Sub

Rob

"leandrocg " wrote in message
...
i know that must have a very obvious solution but,
how do i use two same macros in a sheet?
ex. i use Worksheet_Change

and later Worksheet_Change again
but excel says "repeated name found: Worksheet_Change"

i would like to use Worksheet_Change twice with different properties,
how would i do it?


---
Message posted from http://www.ExcelForum.com/


--

Dave Peterson