VBA Calling Another Sub Routine to Run

Hi,

I have the following code on sheet12 (tab name Monday).....

Please Login or Register  to view this content.

.....which runs when when the sheet is updated. The first part works as required (letters in column E2:E70 are changed to uppercase) however when it gets to the code in 'red', nothing happens.
The SUB routine I am trying to call is 'Private Sub Monday_R11_Average_Call_Time()' and is written within the <ThisWorkbook> object section.

I have tried 'Run Monday_R11_Average_Call_Time','Call Monday_R11_Average_Call_Time (which error's)', 'Application.Run Monday_R11_Average_Call_Time' & 'Monday_R11_Average_Call_Time.Call (which error's)'

Can someone please tell me how I call this SUB routine. Thanks.

THANKS - this is now working but it has led onto another error.....

Please Login or Register  to view this content.

.....when it gets to the section in 'Red' I get the error MSG "Run-time error '1004': Unable to get the SumIfs property of the WorksheetFunction class".

Thanks.

Yes, definetly working before when I 'stepped' through the coding to test.

I've just done a quick test. If I 'Step' through the code it all works whether it is in ThisWorkbook or in it's own module.
Obviously, calling this SUB didn't work when it was in ThisWorkbook so wasn't able to test it in a 'working environment' but having it setup as suggested means that it does work in the 'working environment' and this error's.

Any idea's...??

Norie,

Just tried your suggestion in #6 of this post and it didn't work, error's on

Please Login or Register  to view this content.

I know what the problem is....

Please Login or Register  to view this content.

I have a number of rows, each row has a 'start time', a 'finish time' and the time difference between each of them. The cell S71 is an overall total of the time difference's giving an overall work time.
As these cells are calculations, the cell changes from current entry - to #VALUE! - to new calculation but the WorkSheet_Change function see's the change from current total to #VALUE! so it calls the 'Monday_R11_Average_Call_Time' module but the SUMIFs cannot work as the cell is working on has #VALUE! instead of a numeric figure.

I hope the above makes sense.

Norie,

Thank you SO much for your time. Basically, the issue was that the range I was working with was a range of cells that contained results of a calculation. When the calculation updated it went from current figure to #VALUE! to new figure.
The worksheet_change saw the change from current figure to #VALUE! so the command then started to work but with no figures to work with.

I have got round this by starting to commands during a save procedure instead (which actually turns out to be better than what I was trying in the first place).

Please Login or Register  to view this content.

Unfortunatley, I am unable to attach the spreadsheet into the forum to show (work related and I'm not allowed - sorry).



Thanks again though for all your help, I have certainly learnt a couple of things