I have searched and found nothing to lead me to what I am doing wrong so I must ask a question that appears to have been asked before, though I believe my question is slightly different. The question as asked originally (by someone else) can be found in the thread "VBA How do I set a value in a cell?".
I have a function that is invoked with the following formula in a cell:
=IF(H313="Y",CreateAppointment(B313,B314,A313,"On-Call Schedule",H313),"")
H313 can be any cell on the spreadsheet, but the purpose is to trigger the execution of the function CreateAppointment.
B313 and B314 are the names of the primary and secondary support personnel for the week.
A313 contains the date of the first day of the support week.
"On-Call Schedule" is the name of an outlook calendar that is to have a event inserted containing the support personnel names, etc.
The H313 is the cell identification of the cell containing the trigger flag.
The idea here is that when CreateAppointment is invoked, if the primary and secondary support names are non-blank, and the support date is also non-blank, then an outlook event will be inserted into the designated calendar and a result of "Done" is returned as the function result. Additionally, I want the function to clear the trigger cell, which is why the cell reference appears as the 5th argument to the function.
The outlook functionality works just fine, what is not working is the clearing of the trigger cell.
In the above referenced thread, it appears that the end result was to create a function that invoked a subroutine and the original poster, I believe, indicated that it solved the problem. I have created a similar function/subroutine combination as follows and I cannot make it work. If I comment out the last line before the "End Sub", the processing completes, the function returns "Done" to the cell within which it is coded, but the trigger cell remains "y". If I uncomment out the instruction, the process ends up with #VALUE in the cell where the function is coded.
Bookmarks