Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Stop Rand() Recalc on F9 (Excel 2000)

    Hi,
    Is there a way to prevent =Rand() from recalculating when F9 is pressed? Obviously is must still recalculate on Enter!
    Tx
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop Rand() Recalc on F9 (Excel 2000)

    I guess only by puttin it in a UDF???
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Stop Rand() Recalc on F9 (Excel 2000)

    Are you saying that a UDF does not recalc when the user presses F9!

    Something like:
    Function myRandom()
    myRandom = Rnd()
    End Function
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Stop Rand() Recalc on F9 (Excel 2000)

    An Improved Custom Random Numbers Function gives an example UDF.

    If you remove the Application.Volatile statement then the random numbers will not change unless you do a full recalculation (Control+Alt+F9)

    If you never want the numbers to change you could use Copy and then Paste Special>Values

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Stop Rand() Recalc on F9 (Excel 2000)

    Tx Tony. This is definitely one to save for future use!
    Cheers
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop Rand() Recalc on F9 (Excel 2000)

    Or when a cell that has a reference to the udf changes due to a change in another cell that is a predecessor of the cell with the udf function.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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