There is a problem, which i've been struggling with for some time now.
I've created a workbook with a macro that creates worksheets within
the workbook. The macro also enters formulas refering to cells of
other worksheets.
At first everything seems to work properly in the new worksheet.

The problem is that when I run the macro again to create another
worksheet, the formulas in the previous created worksheet fail to
autocalculate although "autocalculate" is on. Pressing <F9 doens't
work as well. The only thing that works is to enter the cell and then
press <enter. However the formulas in the new worksheet do work
properly, right until another new worksheet is created.

Has anybody encountered this problem, and is so.... any solutions?

Thanks in advance,

Frans Muller

The part of the VBA code that creates the worksheet:

Function Naar_vergelijk()
Dim Naam As String
Dim naam5 As String
If ActiveCell.Row < 6 Or ActiveCell.Row 98 Then
Show_Error ("Blad kan niet worden weergegeven, deze valt
buiten de selectie.")
Exit Function
End If
Naam = SetSheetName
naam5 = "Ink" + Right$(Naam, Len(Naam) - 4)
On Error GoTo Foutafhandeling
Sheets(Naam).Select
x = 0
If x = 0 Then Exit Function
Foutafhandeling:
Werkboek_beveiliging_opheffen
errorhandling Naam
Sheets(Naam).Visible = True
Sheets(naam5).Visible = True
Sheets(Naam).Select
Werkboek_beveiliging_zetten
End Function

Function errorhandling(naam3)
Dim naam4 As String
Dim teller As String
Dim teller2 As String

Resultaat_Beveiliging_Opheffen
naam4 = "Ink" + Right$(naam3, Len(naam3) - 4)
Select Case Error
Case Is = Error(9)
Sheets("Ink_standaard").Copy Befo=Sheets(1)
Sheets(1).name = naam4
Sheets(naam4).Move Befo=Sheets("resultaat")

Worksheets("rekenblad").Cells(3, 1).Value = formule1
Sheets("Verg_standaard").Copy Befo=Sheets(1)
Sheets(1).name = naam3
Sheets(naam3).Move Befo=Sheets("resultaat")

Worksheets(naam4).Range("A6:A42").NumberFormat = "General"
Worksheets(naam4).Range("C6:C42").NumberFormat = "General"
Worksheets(naam4).Range("E6:E42").NumberFormat = "General"
formule1 = "=" + naam3 + "!$A$"
formule2 = "=" + naam3 + "!$F$"
formule3 = "=" + naam3 + "!$N$"
Worksheets("rekenblad").Cells(1, 2).Value =
Worksheets("rekenblad").Cells(1, 1).Value + 5
teller2 = Worksheets("rekenblad").Cells(1, 2).Value
formule4 = "=Keuze!$E$"
onderwerp = "=Keuze!$D$"
For x = 9 To 45

formule = formule1 & x
Worksheets(naam4).Cells(x - 3, 1).Formula = formule

formule = formule2 & x
Worksheets(naam4).Cells(x - 3, 3).Formula = formule

formule = formule3 & x
Worksheets(naam4).Cells(x - 3, 5).Formula = formule
Next x
formule = onderwerp + teller2
Worksheets(naam3).Cells(4, 2).Formula = formule
formule = formule4 + teller2
Worksheets(naam3).Cells(5, 7).Formula = formule

Worksheets(naam4).Cells(3, 2).Formula = formule
Worksheets(naam4).Cells(3, 6).Formula =
Worksheets("rekenblad").Cells(1, 1).Value
Worksheets(naam3).Cells(2, 3).Formula =
Worksheets("rekenblad").Cells(1, 1).Value
Worksheets(naam4).Range("A6:A42").NumberFormat = ";;@"
Worksheets(naam4).Range("C6:C42").NumberFormat =
"#,##0.00_-;[Red]#,##0.00-;;@"
Worksheets(naam4).Range("E6:E42").NumberFormat =
"#,##0.00_-;[Red]#,##0.00-;;@"
regel = Worksheets("rekenblad").Cells(1, 1) + 9
Select Case regel
Case 10 To 38
regel1 = regel
Case 39 To 66
regel1 = regel + 2
Case 67 To 92
regel1 = regel + 4
End Select

Worksheets("resultaat").Cells(regel1, 1) = formule
Worksheets("resultaat").Cells(regel1, 3) = "=" + naam4 +
"!$C$57"
Worksheets("resultaat").Cells(regel1, 6) = "=" + naam4 +
"!$E$57"
Worksheets("resultaat").Cells(regel1, 10) = "=" + naam4 +
"!$C$59"

End Select
Resultaat_Beveiliging_Zetten
Nieuw_Beveiliging_Zetten naam3
Nieuw_Beveiliging_Zetten naam4
End Function

Frans,

Try preceding your code with

Application.Volatile

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frans Muller" wrote in message
om...
There is a problem, which i've been struggling with for some time now.
I've created a workbook with a macro that creates worksheets within
the workbook. The macro also enters formulas refering to cells of
other worksheets.
At first everything seems to work properly in the new worksheet.

The problem is that when I run the macro again to create another
worksheet, the formulas in the previous created worksheet fail to
autocalculate although "autocalculate" is on. Pressing <F9 doens't
work as well. The only thing that works is to enter the cell and then
press <enter. However the formulas in the new worksheet do work
properly, right until another new worksheet is created.

Has anybody encountered this problem, and is so.... any solutions?

Thanks in advance,

Frans Muller

Frans is using 'function' rather than 'sub' in that these
are code modules and not UDF's
Application.Volatile doesn't work.
I suspect that within his code he needs to add something
like

Worksheets(naam4).Calculate

....although if Shift+F9 doesn't work, then this probably
won't

-----Original Message-----
Frans,

Try preceding your code with

Application.Volatile

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frans Muller" wrote in message
. com...
There is a problem, which i've been struggling with
for some time now.
I've created a workbook with a macro that creates
worksheets within
the workbook. The macro also enters formulas refering
to cells of
other worksheets.
At first everything seems to work properly in the new
worksheet.

The problem is that when I run the macro again to
create another
worksheet, the formulas in the previous created
worksheet fail to
autocalculate although "autocalculate" is on. Pressing
<F9 doens't
work as well. The only thing that works is to enter
the cell and then
press <enter. However the formulas in the new
worksheet do work
properly, right until another new worksheet is created.

Has anybody encountered this problem, and is so....
any solutions?

Thanks in advance,

Frans Muller


.

If the formulae refer to the new sheet in anyway, it my
require the file be saved before they will reliably
calculate
-----Original Message-----
Frans is using 'function' rather than 'sub' in that these
are code modules and not UDF's
Application.Volatile doesn't work.
I suspect that within his code he needs to add something
like

Worksheets(naam4).Calculate

....although if Shift+F9 doesn't work, then this probably
won't

-----Original Message-----
Frans,

Try preceding your code with

Application.Volatile

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frans Muller" wrote in message
.com...
There is a problem, which i've been struggling with
for some time now.
I've created a workbook with a macro that creates
worksheets within
the workbook. The macro also enters formulas refering
to cells of
other worksheets.
At first everything seems to work properly in the new
worksheet.

The problem is that when I run the macro again to
create another
worksheet, the formulas in the previous created
worksheet fail to
autocalculate although "autocalculate" is on. Pressing
<F9 doens't
work as well. The only thing that works is to enter
the cell and then
press <enter. However the formulas in the new
worksheet do work
properly, right until another new worksheet is created.

Has anybody encountered this problem, and is so....
any solutions?

Thanks in advance,

Frans Muller


.

.

Thanks for the quick reply.

I did try the application.volatile statement, unfortunately it did not
help.
But I have to say that I'm not sure where to place the statement. I
placed it in the beginning of the Function Naar_vergelijk.

For now, I redesigned the workbook so that it doesn't need to enter
formulas in the worksheet, and that the transportation of the data is
manually by a macro.

Still, it is a problem I've encountered several times now. And the
advantages of entering formulas in a worksheet by macro are too great
to let go, and I hope to find a "cure" someday before I'm old and
grey.

Frans

Hi Frans,

I found it a bit difficult to see what exactly you were trying to do.
Nevertheless, a few general remarks about functions and macros.
You can not change anything in worksheets from within a function, just from
a macro.
All data the function needs to do what it has to do, should be declared as
arguments and given as arguments to the actual function call. Any direct
reference to cells in worksheets will not be recognized bij Excel; therefore
they will not recalculate reliably. "Application.Volatile" is often
recommende to cure this, but there are still doubts as to Excel will know
the order of recalculation. How could it know?
So in general, always pass your data as arguments.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Frans Muller" wrote in message
om...
Thanks for the quick reply.

I did try the application.volatile statement, unfortunately it did not
help.
But I have to say that I'm not sure where to place the statement. I
placed it in the beginning of the Function Naar_vergelijk.

For now, I redesigned the workbook so that it doesn't need to enter
formulas in the worksheet, and that the transportation of the data is
manually by a macro.

Still, it is a problem I've encountered several times now. And the
advantages of entering formulas in a worksheet by macro are too great
to let go, and I hope to find a "cure" someday before I'm old and
grey.

Frans