Holiday Function - Show/Hide 1st of May.

Hi Forum.

This description is in the attached Test too.

I need to show the 1st of May in B4, depending of the value in D10 is Yes or No.

The Case DateSerial(InputYear, 5, 1): HelligdagsNavn = "1. Maj" is working in the Module Function named X_Helligdage_Funktion.

Instead I need something as the two IF THEN below or maybe an IF THEN ELSE instead and only use the Yes.
- Rem If Sheets("Indstil").Range("D10") = "Yes" Then Case DateSerial(InputYear, 5, 1): HelligdagsNavn = "1. Maj"
- Rem If Sheets("Indstil").Range("D10") = "No" Then Case DateSerial(InputYear, 5, 1): HelligdagsNavn = ""

Anybody who can help me define the Yes(No) in the VBA Function ?

Thank you in advance.

Ib

Hi there,

Does the following formula in Cell B4 do what you need?

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg.

Is the code you send a code I can place in a VBA Function ?

I Denmark is the 1st of May is a holiday for some people and a normal working day for others.
The holiday is called "1st of May" as e.g. the 1/1 is called New Years Day.

If you open the attached test sheet, you'll see there is some date's in column A and to the right for the date is the holiday-name in column B.

Depending of the value in D10 is Yes / No, it should show the holiday 1st of May in B4.

Regards
Ib

This attached Test probably show what I mean and need in a better way

Hi there,

Many thanks for that very clear description of your requirements.

Take a look at the attached workbook and see if it does what you need. I have rewritten your "HelligdagsNavn" routine as follows:

Please Login or Register  to view this content.

The optional argument "sYesNo" is used only in Cell B4 (for the date of 1 May yyyy) as follows:

Please Login or Register  to view this content.

where D10 is the cell which contains the Yes / No value


For all other cells the function is entered without the optional argument, e.g.

Please Login or Register  to view this content.

As far as I can tell, it's not really necessary to include your error handling when the function is used, i.e.

Please Login or Register  to view this content.

Please note that in order to get the site to accept this post I have had to use the highlighted "English" letters as replacements for the original Danish ones - the attached workbook contains the Danish characters copied from your workbook.


Hope this helps - please let me know how you get on.

Regards,

Greg M

I had been thinking if something like this can be used, but I can't get i work

Please Login or Register  to view this content.

Ib

Hi again,

Thanks for your feedback.

I'll have a look to see what I can do, but I'll be away for a few days so it will probably be Monday/Tuesday next before I can get back to you.

Regards,

Greg M

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome, and congratulations on sorting out the finer points out by yourself.

Best regards,

Greg M