Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a spread sheet that automatically calculates recommended quantity for an order. However, sometimes the client wants to change the order quantity and I can overwrite the calculated amounts. Problem is that sometimes the client wants to go back to the calculated amount but since I have written over the formulas I can't do it. I would like to have a button that says RESET so I can click it and reset those cells to their original state (the formulas). I have attached a small spreadsheet that illustrates this.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd have to program the formulas explicitly:
    Code:
    Sub ResetFormulas()
      Dim r As Long
      For r = 6 To 8
    	Worksheets("Sheet1").Range("C" & r).Formula = "=B" & r & "*$C$3"
      Next r
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans, then how would i attach it to a button? In my example, I just created a shape and it seems I can only attach a macro. How would I attach this code to the button?

    [quote name='HansV' post='762995' date='03-Mar-2009 01:33']You'd have to program the formulas explicitly:
    Code:
    Sub ResetFormulas()
      Dim r As Long
      For r = 6 To 8
    [tab][/tab]Worksheets("Sheet1").Range("C" & r).Formula = "=B" & r & "*$C$3"
      Next r
    End Sub
    [/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Don_Sadler' post='762999' date='03-Mar-2009 09:23']Thank you Hans, then how would i attach it to a button? In my example, I just created a shape and it seems I can only attach a macro. How would I attach this code to the button?[/quote]
    The code that I posted *is* a macro, so:
    • Right-click the shape.
    • Select Assign Macro... from the popup menu.
    • Select ResetFormulas from the list of macros.
    • Click OK.
    That's all!

    Note: the ResetFormulas macro should be in a standard module in the workbook, not in the worksheet or ThisWorkbook module.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Beautiful! Thanks you so much.

    [quote name='HansV' post='763000' date='03-Mar-2009 02:49']The code that I posted *is* a macro, so:
    • Right-click the shape.
    • Select Assign Macro... from the popup menu.
    • Select ResetFormulas from the list of macros.
    • Click OK.
    That's all!

    Note: the ResetFormulas macro should be in a standard module in the workbook, not in the worksheet or ThisWorkbook module.[/quote]

Posting Permissions

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