Opening a workbook from a cell condition

[northernstar197]

This problem is not for the faint hearted.........only read on if you are extremely good with Excel or are brave or both......

here goes

i have a workbook which consists of about 5 or 5 sheets each containing a list of 40 employees and a list of tasks ranging form about 10 upto 150. the purpose of this worksheet is to record and monitor the competency and training via a matrix and a record for each member of staff.

the problem we have is updating and keeping this workbook upto date

previously we had the matrix in Excel and the records in Word, i am in process of changing this so any changes to the record is updated on the matrix automatically.....

this is done in three stages

1) training planned
2) training completed - but needs practise
3) competent

each time a stage is completed the cell changes colour

the problem we have is that we would like to set a timescale for each stage, for example 6 months for stage 1 & 2 and 2 years for stage 3

for each time a stage exceeds this time scale we would like an email set to the relevant manager as a reminder

the workbook is NOT open all the time

i hope i have included all the necessary information

please dont hesitate to contact me for any required information

i am not able to supply the actual worksheet but maybe able to supply a sample one if needed

i look forward to hearing any solutions on the following

many thanks

[Simon Lloyd]

Hi, check out the attached, the code checks the cells in the range for a date and background colour, if these match the criteria then an e-mail is generated sending the employees name and date they were lst trained.

Of course you can modify these, i haven't set a worksheet so it will run on whichever sheet is active.

[northernstar197]

Hi simon

thanks for that, not had a chance to check it out properly yet but it appears to be good to go

i assume this will work when the workbook is opened?

if not when and does it work?

thanks again

[Simon Lloyd]

I doesnt strt with the workbook opening you need to trigger it via a command button or such like, perhaps worksheet activate, you could put this in the thisworkbook module

Please Login or Register  to view this content.

when the workbook is opened the code will initiate, however as i said i have not set a worksheet so it will work with the activesheet!

Regards,
SImon

[northernstar197]

Hi simon

thanks for that, it was just as thought

but i can set a windows task to open the workbook on a monthly basis and then trigger the macro from 'open'

sure this will be ok, what do you think?

thanks again

[Simon Lloyd]

Yes of course you could but remember i did say i havent set a worksheet for it to work on so unless its the only sheet in your book you need to specify it. you could do with having another column with the managers e-mail address for that employee there, that way in the send mail where you need an e-mail address you can just use the offset range for the "Who to".

[northernstar197]

yes i remebered that you havent specified a worksheet

i will have a play around and let you know if i need anymore assistance

thanks again