Thanks Peter,

I tried what you suggested but Access didn't
recognize .drawingObject.Formula, I know you wrote untested so I'm
guessing thats why. Your second solution however did work. So I no
longer get that issue with inputting the formula however when doing
that you showed I actually had two problems instead of 1 because I
still get that error message every other time but the formula fills in
everytime.

I already had the excel reference to xlCategory I use it many times in
the code before that happens, so I don't think thats it. The code
thats giving me the issue doesn't execute 'til right. If you have any
more suggestions they will be greatly appreciated.

However if not thanks a lot for the formula suggestion I'd rather that
work everytime anyway even if the user still has to close the excel
sheet in the event of an error.