ToggleButton to turn HasFormula off/on

[vwhite]

Hi All

I am hoping someone can help. I am using the following function along with some conditional formatting to show which cells in a range contain a formula:

Please Login or Register  to view this content.

But for some reason this causes the [Workbook_Open] and [Workbook_Activate] events not to trigger when the workbook is opened??

I've been playing with several workarounds but think the easiest fix would be to toggle HasFormula on/off as needed. I've added a toggle button to the sheet:

Please Login or Register  to view this content.

But I can't figure out how to replace the message box with the HasFormula function?

Any help would be greatly apprecaited

Thanks all
Vicky

[YasserKhalil]

Hello
Can you provide sample of your workbook and what are you trying to do?
I don't think that the UDF will affect the events in this workbook module

[mjr veverka]

... But for some reason this causes the [Workbook_Open] and [Workbook_Activate] events not to trigger when the workbook is opened?? ...

1. What does it mean ? What did you want to do ?
2. Rename the function - HasFormula is reserved word in vba.
3. In some cases you will have a different result than you would like (you will get "0"), because ... - see info from the context help function:

[vwhite]

Hello
Can you provide sample of your workbook and what are you trying to do?
I don't think that the UDF will affect the events in this workbook module

Hi YasserKhalil

Sorry about this, I', having trouble uploading the workbook
It was about 3,500KB
But I got it down to 2,300 after removing any sensitive data and putting it in a zip file

The max file size for zips is 9.77MB so it should be working but keeps saying 'Upload failed'
I tried 3 different web browsers?

I will persevere and keep you posted

[YasserKhalil]

First keep just some little rows in your file so as to make it smaller in size then compress the file in ZIP format if you find it still bigger

[vwhite]

Hello
Can you provide sample of your workbook and what are you trying to do?
I don't think that the UDF will affect the events in this workbook module

Ok I've got it. File now uploaded
And I've also made a curious discovery about precisely when this issue occurs

I use the attached spreadsheet to process invoices onto Sage.
The main sheet "INV & CRN" has conditional formatting in columns G:G and N:P to change the background colour of any cells containing a formula.

This conditional formatting is linked to the HasFormula VBA function in module 4

While the HasFormula function is present, none of the code in 'This Worksheet' section triggers when the workbook is opened?
Workbook_Open() never runs
And Woorkbook_Activate() only runs if you open another workbook then tab back to this one

However, if you go to module 4, comment out the code, save, close and re-open the workbook, both jobs (Workbook_Open and Workbook_Activate) run immediately as the workbook opens

Also I have just made a little discovery.
My macro security settings are set to "Enable All Macros" but if you change them to "Disable all Macros With Notification" when you download and open the workbook for the first time, and you see the yellow message bar along the top saying "Security Warning. Some active content has been disabled. Click for more details. [Enable Content]" , after clicking enable that first time, everything works perfectly. Both Workbook_Open and Workbook_Activate codes fire, and the HasFormla colour formatting is on.

But if you close the workbook and reopen it, it reverts back to not working

If there is any way to toggle the HasFormula on/off (as that's what seems to be what's causing the problem when opening) it would be so helpful

Much appreciated
Vicky