Worksheet_Change Event

[popper]

I have read some of the responses to similar questions regarding Worksheet_Change routines that allow macros to be activated upon change to a cell. However, when I run this, (say the following), I get a

'argument not optional' message. Can anyone help?

With thanks



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox ("hello")
End Sub

 

[SkipVought]

Hi,

However, when I run this...

Exacely HOW did you "run" this?

It is NOT something that you "RUN."

It fires, when a change is made to a range on the associated Worksheet.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[popper]

Oh...I meant when I change a cell, the code runs. However, unless I make the parameter 'Target' Optional, I get the 'Argument not Optional' message. However, when I do this, 'Target' is not recognised and I get a 'Object Variable or With block variable not set'. I am doing something wrong. It seems to make no difference whether I include a

" ThisWorkbook.Worksheets("Calculate_Payment").OnEntry = "Worksheet_Change"

in an Auto-Open subroutine in the module.

I have had to include the routine with the parameter made optional but with no code in it at all. It seems that I have to have this subroutine in my module or else a message appears saying that it cant be found. This may be because I have a similar one in the Sheet code which is tied to a Validation drop down menu which does work.

It is all very puzzling. It would be great if you could set me straight on all this.

 

[SkipVought]

unless I make the parameter 'Target' Optional

The Worksheet_Change event code stup is ALREADY in the Sheet Object Code Window. You do not have to add this code!!!

Right-click the Sheet Tab and select View Code

You can now see that Sheet's Code Window.

Above the Sheet Code Window are 2 drop down controls.

In the LH control, select Worksheet.

In the RH control, select Change.

This is the Worksheet_Change event. Put your code in this code stub procedure.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[popper]

Yes SV, this is how I created the Worksheet_Change routine in the Sheet code. However, as I said, it appears that I need another one with no code within its body in Module1 in order to avoid error messages. I don't know why.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)[b]
  msgbox "hello world"[/b]
End Sub

This part is the only thing that you have to enter

Where in the world did you get [red]Excel.[/red]Range???

Nothing need be in a MODULE.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[popper]

lol...I saw Excel.Range used like this in a Worksheet_Change routine on some Excel site somewhere and in my desperation I used it.

Ok. I will try all this again...but do I need the Auto-open routine?

Thanks

 

[SkipVought]

No.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[popper]

The Worksheet_Change routine in the Worksheet code does pick up changes caused by a validation selection list cell. I have this:

Private Sub Worksheet_Change(ByVal Target As Range)
Call Module1.WChange(Target)
End Sub

where WChange does the work reacting to the validation dropdown change. However, it does not work if I try ordinary data entry. Then I get the message

"The macro 'Myfile.xls!Worksheet_Change' cannot be found"

However, if I include the following:

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

in Module1 it works fine and I pick up any type of change.

A typical thing I do in WChange is

Application.EnableEvents = False
ActiveWorkbook.Sheets("Calculate_Payment").Range("e10") = ""
Application.EnableEvents = True

in order to avoid an infinite loop as I worry that the Work_Change routine will trigger again.

I hope this gives you some clue as to what is going on.

Thanks for your interest and responses.

 

[combo]

The 'OnEntry' property or 'Auto-Open' procedure is an old way of working with events. In ancient time of excel 5/95 there were no document or class modules, all the code had to be in standard modules (or rather special sheets, btw there were only different sheet types). As a consequence you were setting a standard procedure that was called when cell formula/data was changed.
Now you can use document modules to handle events, a sheet module for sheet level events and ThisWorkbook module that handles workbook level modules.
Follow Skip's hints in building event procedures, in case of the error - remove 'OnEntry' assignment and write all code in the 'Worksheet_Change' automatically generated procedure (unless you need a code to work with several sheets).


combo

 

[SkipVought]

Are you trying to call the Worksheet_Change event from Module1?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[popper]

No, I am trying to prevent that with the

Application.EnableEvents = False

switch.