Oh god, it is, I checked it again, and during the repeated test I discovered
the cause of the problem: a control (checkbox) was active and not a
worksheet. Having clicked on the worksheet F9 worked again as prescribed.
What do you think, what is the easiest way to avoid this situation?

Stefi


€žCharles Williams€� ezt Ã*rta:

Are you sure your fileexists function is ?
- volatile
- used on worksheet Seged


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Stefi" wrote in message
...
Hi Charles,

Your suggestion almost solved my problem. One question remained: pressing
F9
doesn't fire Worksheet_Calculate of Seged if nothing else changes but the
existence of files.

Any further idea?

Regards,
Stefi


"Stefi" ezt Ã*rta:

Many thanks, Charles, I'm going to try it and let you know the result.
Stefi


"Charles Williams" ezt Ã*rta:

Hi Stefi,

I understand.

Here is what you need to do.

1. copy the workbook_SheetCalculate code
2. In the VBE under VBAProject in the Project Window right-click Sheet
"seged" and select view code
3. On the Seged code module that you have got to create a
worksheet_Calculate sub
4. Paste in the code from Workbook_SheetCalculate
5. Remove the Workbook_SheetCalculate code.

This will stop the workbook_Calculate sub from executing after every
sheet
recalcs, and make the code only calculate after the Seged sheet is
calculated (which should be only once per F9).

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Stefi" wrote in message
...
Hi Charles,

Thank you for your post, I was giving up hope to solve this problem.
I try
to put clear the task:

I have a range named "megvane" in sheet "seged" containing
=Fileexists(...)
UDFs.
Fileexists is volatile because it is required to evaluate these cells
when
pressing F9.
What I'd like to happen when pressing F9 is first to check the
existence
of
files by evaluating =Fileexists(...) in range "megvane" then
enabling/disabling commandbuttons depending on the values of range
"megvane".

The problem is that changing the values in range "megvane" also fires
Workbook_SheetCalculate event so it is executed as many times as many
cells
range "megvane" has plus once for Calculate triggered by F9. I tried
to
place
Application.EnableEvents = False as the first line of
Workbook_SheetCalculate
code, it solved the problem, but cannot find the place to switch it
back
to
True. Placing it as the last line of Workbook_SheetCalculate code re
creates
the problem.

Regards,
Stefi




"Charles Williams" ezt Ã*rta:

Hi Stefi,

Not sure I understand exactly what you are trying to do, but it
seems to
me
that you only want the workbook_sheetcalculate sub to run when
FileExists
has been calculated.

The simplest way to do this is to create a WorkSheet_calculate sub
in the
code behind worksheet Seged

Or you could check the name of the sheet inside the

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
if sh.name<"seged" then exit sub
....

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Stefi" wrote in message
...
Hi Norman.

I tried to understand your suggestions, but I couldn't apply them
to my
case. So I post the code of Workbook_SheetCalculate and the UDF
hoping
you
can give me some more useful advices.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Worksheets("Jelentés").CommandButton1.Enabled = _
Names("megvane").RefersToRange.Item(1, 1) And _
Names("megvane").RefersToRange.Item(1, 2) And _
Names("megvane").RefersToRange.Item(2, 1) And _
Names("megvane").RefersToRange.Item(2, 2)
Worksheets("Jelentés").CommandButton3.Enabled = _
Names("megvane").RefersToRange.Item(2, 3)
Worksheets("Jelentés").CommandButton2.Enabled = _
Names("megvane").RefersToRange.Item(3, 1) And _
Names("megvane").RefersToRange.Item(3, 2)
Worksheets("Jelentés").CommandButton4.Enabled = _
Names("megvane").RefersToRange.Item(3, 3)
End Sub

The UDF:
Public Function FileExists(fname, Optional most As Date) As
Boolean
Application.Volatile
FileExists = False
On Error Resume Next
FileExists = IIf(Dir(fname) < "", True, False)
End Function

Range "megvane" is in another sheet named "segéd", range("G3:I5"),
the
first
item (G3) contains
=fileexists(Jelentés!$B3&Jelentés!G3)

which is filled to the right and down throghout the range.
Jelentés!$B3 contains folder name,
Jelentés!G3 contains file name
of files existence of which is to be checked. The do not change,
therefore
UDF is not recalculated. I'd like that the UDF be recalculated
when
pressing
F9, to re-check the existence of the files.

How can I do that without Volatile

Regards,
Stefi

"Norman Jones" ezt Ã*rta:

Hi Steffi,

Without seeing your UDF, it is difficult to
give a useful response.

However, you can set the Application.Volatile
statement to false in your UDF.

Also, you should pass all references to cells
as parameters to your UDF. In this way, the
UDF will be recalculated whenever any of
the cells change. This will obviate the need
for an Application.Volatile statement which
is slow.



---
Regards.
Norman


"Stefi" wrote in message
...
Hi Norman,

Im the meantime I discovered that Workbook_SheetCalculate event
was
triggered not by enabling/disabling a commandbutton but by a
volatile
UDF
in
some cells. Pressing F9 started Calculation (and triggered
Workbook_SheetCalculate first time)therefore the volatile UDF
was
evaluated
changing cell values, changing cell values started a new
Calculation
which
triggered Workbook_SheetCalculate second time.

The question is now how can I reduce No of executing
Workbook_SheetCalculate
to one? I do need volatile nature of the UDF!

Regards,
Stefi

"Norman Jones" ezt Ã*rta:

Hi Steffi.

Try posting the code for the CommandButton.



---
Regards.
Norman


"Stefi" wrote in message
...
Hi All,

Is a Workbook_SheetCalculate event triggered by
enabling/disabling a
control, e.g. a commandbutton? I have one doing nothing
else, and
it
seems
to
trigger itself again. Is it possible? If so, how can I avoid
it
to
happen?

Thanks,
Stefi