Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Question Unanswered: Refresh Cells upon opening Excel file

    Hi all,

    I had an Excel file with a few cells using some User defined functions that I had wrote. But I does not seems to recalculate when ever I open this Excel file. Can anyone please advise on what I can do so that these cells will auto refresh the calculation.

    Thanks alot.

  2. #2
    Join Date
    Oct 2003

    Check whether by chance in the options menu recalculation is set to manual instead of automatic.
    Otherwise send a dummy parameter to the function with reference to the cell that is changed. You may ignore this parameter in your function, but while callin the function on the cell, the parameter should be passed. For example if you are expecting some change in some value in some cell when value in A10 changes, you can pass an argument to the function as A10.
    i.e. your function for example may be like

    Public Function myfunc() As Integer
    'just double the value of A10 and place it wherever the function is called
    myfunc = Sheet1.Cells(10, 1) * 2
    End Function

    You may have put in your some cell =myfunc()
    This will not be refreshed automatically even if the value in A10 changes.

    For this you may change the function to

    Public Function myfunc(dummy As Range) As Integer
    myfunc = Sheet1.Cells(10, 1) * 2
    End Function

    And wherever your are putting the function as =myfunc() put it as =myfunc(A10)



  3. #3
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    Although I have not tried it, but, would putting 'Application.Calculate' in the Workbook objects Workbook_Open() event update the worksheet when it opens??


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts