Workbook_SheetChange not working
 

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

Workbook_SheetChange not working
 

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"

wrote in message

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

Workbook_SheetChange not working
 

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" wrote in message
...
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"

wrote in message

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

Workbook_SheetChange not working
 

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

Workbook_SheetChange not working
 

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

Workbook_SheetChange not working
 

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

Workbook_SheetChange not working
 

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" wrote in message
...
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

Workbook_SheetChange not working
 

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