Excel XP & Win XP
I'm at my wits end with this one.
I have two macros:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$N$1" Then Call Hello(Target.Value)
End Sub

And
Sub Hello(Doodle As String)
MsgBox "Working Hello"
Application.EnableEvents = False
Range("N1").ClearContents
Application.EnableEvents = True
End Sub

N1 is a Data Validation cell.
The second macro sets a range, sorts the range, and clears N1. The sort is
not working and N1 does not get cleared. I reduced the second macro to what
is shown for troubleshooting.
The MsgBox "Working Hello" is displayed so the second macro is being
executed.
The module name is not the same as any macro name.
I copied the code of the second macro to a text file (Word), removed the
module, inserted a new module, named it SortByChoice as before. Copied code
from the Word file to the module. Same problem.
I took the code, all of it, and copied it to a blank file module. I typed
something in N1, hit Enter, and it was cleared just as it's supposed to be.
The code is good code, obviously, but not in my file.
Question: What am I dealing with here and how can I fix it? Thanks for
your help. Otto

I would try Rob Bovey's Code Cleaner. It's cured a bit of insanity for
me at times.

http://www.appspro.com/utilities/utilities.asp

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
On Thu, 18 Sep 2003 16:47:40 -0400, "Otto Moehrbach"
wrote:

Excel XP & Win XP
I'm at my wits end with this one.
I have two macros:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$N$1" Then Call Hello(Target.Value)
End Sub

And
Sub Hello(Doodle As String)
MsgBox "Working Hello"
Application.EnableEvents = False
Range("N1").ClearContents
Application.EnableEvents = True
End Sub

N1 is a Data Validation cell.
The second macro sets a range, sorts the range, and clears N1. The sort is
not working and N1 does not get cleared. I reduced the second macro to what
is shown for troubleshooting.
The MsgBox "Working Hello" is displayed so the second macro is being
executed.
The module name is not the same as any macro name.
I copied the code of the second macro to a text file (Word), removed the
module, inserted a new module, named it SortByChoice as before. Copied code
from the Word file to the module. Same problem.
I took the code, all of it, and copied it to a blank file module. I typed
something in N1, hit Enter, and it was cleared just as it's supposed to be.
The code is good code, obviously, but not in my file.
Question: What am I dealing with here and how can I fix it? Thanks for
your help. Otto