Workbook_SheetChange not working

[RAP]

It was working, but I changed a cell entry to a formula and now it won't run.
The two macros I call in the Sheetchange macro run fine when run manually.
Here's the code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
VRange = Range("B3:B7")
Call CheckCellColor
Call RoundOff
End Sub

What am I overlooking? Thanks for any help. - Randy

[Jim Cone]

Randy,

My event code is located in the "ThisWorkbook" module and looks like this....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

Also, does this serve any purpose? ..."VRange = Range("B3:B7")"

Jim Cone
San Francisco, USA


"RAP"
<[email protected]>
wrote in message
news:[email protected]
It was working, but I changed a cell entry to a formula and now it won't run.
The two macros I call in the Sheetchange macro run fine when run manually.
Here's the code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
VRange = Range("B3:B7")
Call CheckCellColor
Call RoundOff
End Sub
What am I overlooking? Thanks for any help. - Randy

[Bob Phillips]

Using Source instead of Target is of no relevance, but I would also be
interested to know how the called procedures know which cells are being
changed.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Cone" <[email protected]> wrote in message
news:%[email protected]...
> Randy,
>
> My event code is located in the "ThisWorkbook" module and looks like
this....
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
> End Sub
>
> Also, does this serve any purpose? ..."VRange = Range("B3:B7")"
>
> Jim Cone
> San Francisco, USA
>
>
> "RAP"
> <[email protected]>
> wrote in message
> news:[email protected]
> It was working, but I changed a cell entry to a formula and now it won't
run.
> The two macros I call in the Sheetchange macro run fine when run manually.
> Here's the code:
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As
Range)
> VRange = Range("B3:B7")
> Call CheckCellColor
> Call RoundOff
> End Sub
> What am I overlooking? Thanks for any help. - Randy

[RAP]

My bad. Here's the code now. It still is not running when a change is made
on the sheet. The code is located in Excel Objects, Sheet 2.

Private Sub WorkSheet_Change(ByVal Source As Range)
Source = Range("B3:B56")
Call CheckCellColor
End Sub

[RAP]

Something else is going on. I opened another functioning program that uses a
worksheet change event and it doesn't work either. Is there some way this
function was "turned off"?


"RAP" wrote:

> My bad. Here's the code now. It still is not running when a change is made
> on the sheet. The code is located in Excel Objects, Sheet 2.
>
> Private Sub WorkSheet_Change(ByVal Source As Range)
> Source = Range("B3:B56")
> Call CheckCellColor
> End Sub

[Dave Peterson]

Since you're using worksheet_change, your code has to be in the module for that
sheet. (Did you move it from the ThisWorkbook module?)

Also, if events are turned off:
application.enableevents = false

They'll have to be turned back on for your code to automatically run with the
next change.

But I wouldn't change the declaration of that procedure.

Private Sub Worksheet_Change(ByVal Target As Range)

And if you want to only look at cells within a certain range, you can use:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub 'one cell at a time
if intersect(me.range("b3:b56"),target) is nothing then exit sub
'...

But who knows what's in that other subroutine???

RAP wrote:
>
> Something else is going on. I opened another functioning program that uses a
> worksheet change event and it doesn't work either. Is there some way this
> function was "turned off"?
>
> "RAP" wrote:
>
> > My bad. Here's the code now. It still is not running when a change is made
> > on the sheet. The code is located in Excel Objects, Sheet 2.
> >
> > Private Sub WorkSheet_Change(ByVal Source As Range)
> > Source = Range("B3:B56")
> > Call CheckCellColor
> > End Sub

--

Dave Peterson

[Bob Phillips]

It seems to me that you have a mis-understanding of event, how they work
etc.

Source (or Target or whatever it is called) is a range, and so it needs to
be Set, not just assigned. Also, that range is passed to the procedure, so
why would you want to over-write it?

I suggest that you read up on it at http://www.cpearson.com/excel/events.htm

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAP" <[email protected]> wrote in message
news:[email protected]...
> Something else is going on. I opened another functioning program that
uses a
> worksheet change event and it doesn't work either. Is there some way this
> function was "turned off"?
>
>
> "RAP" wrote:
>
> > My bad. Here's the code now. It still is not running when a change is
made
> > on the sheet. The code is located in Excel Objects, Sheet 2.
> >
> > Private Sub WorkSheet_Change(ByVal Source As Range)
> > Source = Range("B3:B56")
> > Call CheckCellColor
> > End Sub

[Gary's Student]

Try worksheet_calculate instead of worksheet_change
--
Gary's Student


"RAP" wrote:

> It was working, but I changed a cell entry to a formula and now it won't run.
> The two macros I call in the Sheetchange macro run fine when run manually.
> Here's the code:
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
> VRange = Range("B3:B7")
> Call CheckCellColor
> Call RoundOff
> End Sub
>
> What am I overlooking? Thanks for any help. - Randy