triggering Workbook_SheetCalculate

  • Thread starter Thread starter Stefi
  • Start date Start date
S

Stefi

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,

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.
 
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,

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
 
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
 
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,

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
 
Many thanks, Charles, I'm going to try it and let you know the result.
Stefi


„Charles Williams†ezt írta:
 
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:
 
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:
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Handling a worksheet calculate event in XLA code 4
Workbook_SheetCalculate Events 3
Excel 2003 VBA: Workbook_SheetCalculate problem. 1
On-sheet activeX component disables copy/paste 6
SheetCalculate fires all the time 3
Exclude cells from calculation? 1
Workbook Event 1
HOW: Click Formula Hyperlink To Trigger Macro 1

Back
Top