Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_SheetChange | Excel Programming | |||
Worksheet_Change and Workbook_SheetChange not firing | Excel Programming | |||
Workbook_sheetChange event | Excel Programming | |||
workbook_sheetchange | Excel Programming | |||
Workbook_SheetChange will not fire | Excel Programming |