MichaelH
02-03-2005, 08:19 AM
Hi Guys,
Could anyone help me understand a puzzler please?
What I have got is a functionality in the BEFORE CLOSE Event on a s/s that :
Checks that a specific Add-In is installed, and if so ?
Uses a Function(A) in the spreadsheet module that then uses another Function(B) in the Add-In repeatedly in a Loop to check the validity of specific cells, each time returning a Boolean True / False as to the validity of that cell. If any cell is invalid then FunctionA returns a False to the BEFORE CLOSE which sets CANCEL to True so that the s/s doesn?t close.
This works most of the time, but even when everything is OK it sometimes doesn?t close (I also have displayed messages on each invalid entry found so I know if all should be OK). This seems to be random, and sometimes if I just open the s/s and immediately go to Close it.
What I don?t understand is:
a. when this doesn?t work, if I open the File menu, I see a square box with a tick in it to the left of the Close option and the only way I can Close is to alter something in any VBA module which then gives me a message saying the project will be reset ? it?s then OK.
What is the Square Ticked box? If it?s supposed to say that Close is not available then why isn?t it a cross or greyed out?
The above was complicated in that I had to insert an intermediary Function between the BEFORE CLOSE Event and the Add-In because even though I am testing for the presence of the Add-In before using a Function within it, if the Add-In is not present it falls over because it knows that the Function would not be found ? is there a better way of doing this?
Should I set Application.EnableEvents to False at the start of the BEFORE CLOSE event ?
Any advise appreciated, point me in the right direction.
PS the S/S is huge and holds confidential info. So I?m not able to issue it.
Could anyone help me understand a puzzler please?
What I have got is a functionality in the BEFORE CLOSE Event on a s/s that :
Checks that a specific Add-In is installed, and if so ?
Uses a Function(A) in the spreadsheet module that then uses another Function(B) in the Add-In repeatedly in a Loop to check the validity of specific cells, each time returning a Boolean True / False as to the validity of that cell. If any cell is invalid then FunctionA returns a False to the BEFORE CLOSE which sets CANCEL to True so that the s/s doesn?t close.
This works most of the time, but even when everything is OK it sometimes doesn?t close (I also have displayed messages on each invalid entry found so I know if all should be OK). This seems to be random, and sometimes if I just open the s/s and immediately go to Close it.
What I don?t understand is:
a. when this doesn?t work, if I open the File menu, I see a square box with a tick in it to the left of the Close option and the only way I can Close is to alter something in any VBA module which then gives me a message saying the project will be reset ? it?s then OK.
What is the Square Ticked box? If it?s supposed to say that Close is not available then why isn?t it a cross or greyed out?
The above was complicated in that I had to insert an intermediary Function between the BEFORE CLOSE Event and the Add-In because even though I am testing for the presence of the Add-In before using a Function within it, if the Add-In is not present it falls over because it knows that the Function would not be found ? is there a better way of doing this?
Should I set Application.EnableEvents to False at the start of the BEFORE CLOSE event ?
Any advise appreciated, point me in the right direction.
PS the S/S is huge and holds confidential info. So I?m not able to issue it.