VB function interfere with Excel refresh

[ame]

Hello folks,

I have a small function that creates a filter criteria for a report that links through a access query.When I do a refresh to update values on the report with the new values stored in access database, it throws an exception "Type Mismatch" on the VB function. Below is the function:

Please Login or Register  to view this content.

So, when I do a refresh from Data tab -> Refresh, the function should not throw an exception. Please advise the resolution.

The error is thown on :

Please Login or Register  to view this content.

Thanks for your response.

[OnErrorGoto0]

On the assumption that your code is supposed to be testing if C2 or D2 change (though that is not what it actually does now)

Please Login or Register  to view this content.

[ame]

Thanks..that worked

[ame]

I understand you know this stuff really well, Please help with another requirement.
If you see above, Cell referece "D2" checks for a date value from a pop up calendar i have on that cell. Currently, it filters only for that selected value. However, I need it to display all values "LESS" than the selected date value. Please advise,
Example: If the user selects 1st february 2012 on "D2", the filter should display all rows with date BEFORE 1st february 2012, currently it gives all rows with the date as 1st february 2012.

Thanks for your help

[OnErrorGoto0]

I would have the popup calendar actually enter the date in a different cell (eg D3) and then use ="<"&D3 in D2 for example as the actual filter. You will need to adjust the code here to monitor the new linked cell rather than D2.

[ame]

Regret, I couldnt quite understand the change you propose to make in excel, Please elaborate with an example

[OnErrorGoto0]

I did give an example. Instead of linking your calendar to cell D2, link it to D3, and link D2 to D3 using the formula I suggested.
You then need to change the code to watch D3 rather than D2.

Either that or use an autofilter instead which is probably simpler.

Hard to be any more specific without a workbook.

[ame]

Works great..Thank You..Solved

[OnErrorGoto0]

Glad to help.

From the FAQ:

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save