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

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

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

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

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

Hi Steffi,

I removed the Application Volatile instruction
from your UDF.

Changing the values of (say) cells B3 or G3 on
the Jelentés worksheet updates the cell containing
the formula:

=fileexists(Jelentés!$B3&Jelentés!G3)

on the Segéd sheet



---
Regards.
Norman


"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

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

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

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

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

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

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

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