"Reset" back to initial values

[bunnyball88]

I am creating a dashboard, governed by certain assumptions. I'd like for my customer to be able to change those assumptions (e.g. change the price of the widget to see how that changes the output in the dashboard), and futz around, but then, if they want, be able to "hit a button" and have it re-install the values and / or formulas that formed the initial assumptions.

Is there a way to do that?

Supernew to VBA and macro's so I apologize in advance if this is either very basic or, if upon explanation, i ask for more clarity!

Thank you!

 

[Exceluser616]

Hi bunnyball,

It is simple to put a button onto a worksheet that runs a macro, and it is fairly simple to write a macro that will input formulas and values into the cells that you want. If you are going to let clients overtype your data with changes, is it fair to assume that most of these will be static values as opposed to formulas?

As a most basic example, the following code will input the value 12 into cell A1 of the active sheet whenever the code is run:


Sub RESET()

Range("A1").Value = 12

End Sub

 

[bunnyball88]

Hello ExcelUser616!

If it makes it meaningfully easier, then I could wind up inputting the assumptions they are able to write over as static at the end (for now, I am keeping them dynamic because we are still playing with our base assumptions and i don't want to have to continually hardcode).

Is way to have it so that the reset refers back to another cell (vs. a value)? and can i do it across a range so that B2 will look to A2, B3 to A3, etc?

Thank you again!
BB

 

[Jonmo1]

I would create 2 sheets...
One Master, and one that users can "futz" with.

Make them Identicle.


Then you can run a macro that just copies the master over the top of the user's.

Sub Reset()
Sheets("Master").Cells.Copy Destination:= Sheets("Futzwithit").Range("A1")
End Sub

You can even Hide the master sheet, and the code will still work.

 

[mikerickson]

Is this dashboard a worksheet or a userform?

 

[bunnyball88]

This is incredibly helpful and makes a lot of sense....
Next questions:
1) Is there a way i can have it so that i can have separate buttons to reset certain "sections" within the same page-- e.g. i want them to be able to play with variables related to widget 1 (in a certain section of the worksheet), or widget 2 (in another) and reset them separately.... possible? If not, not a big deal, it's definitely "nice to have" vs. "must have".
2) How do i actually enter the above macro and link it to a button?

Thank you!

 

[bunnyball88]

Is this dashboard a worksheet or a userform?

Define the difference?

Thank you!

 

[Jonmo1]

Sure, you can drill that code down to specific ranges..

Again based on having 2 identicle sheets..

Sub Reset()
Sheets("Master").Range("B20:G40").Copy Destination:= Sheets("Futzwithit").Range("B20:G40")
End Sub

Or even have it just replace whatever section the user currently has highlighted...

Sub Reset()
Sheets("Master").Range(Selection.Address).Copy Destination:= Sheets("Futzwithit").Range(Selection.Address)
End Sub

 

[bunnyball88]

How do you suggest I enter it? I am going through what I understand to be the right steps - creating a new module, pasting in the code, adjusting the ranges and sheet titles as appropriate - but i am getting "Run-time error '1004': Copy method of range class failed"....
thoughts?

(thank you!)

 

[Jonmo1]

Can you post the code as you're trying it now?