macro to reset worksheet to pre-use state

[enxocoebl]

hey ex(cel)perts,
i was wondering if there was a way to return a worksheet to its pre-use state without closing it. i have prepared an invoice sheet and it has dynamic ranges so the total, discount and stuff like that move one row down as one row of products purchased is added on top. for the next invoice i ll have to exit without saving and then reopen.. i was wondering if there was a macro to reset everthing on the sheet to its original unused state.

 

[Hermanito]

Then I suspect it's all conditional formatting.

I should really have a look at the actual file you have, if that is possible for you. You should certainly remove any personal or confidential information first of course...
With the actual file, I could better see what option would be best...

I'll send you a PM with my emailaddress, you can send me the file there...

 

[enxocoebl]

i really think ur idea of having a unused file and overwriting info onto the used file macro will work and is just what i need. i ve been searching for questions in other forums also.. most are different as their needs are a bit different and an ordinary reset page macro is all they need.
have u sent ur email address already?? where can i find it? that file is abt 29mb in size owing to the data i put in the stock tab. i ll erase unwanted data b4 sending u. there is a macro on the customer tab.. its a recording that seems to work.
on the whole its still a work in progress. still have to work on the profit /loss calculations stuff.

 

[Hermanito]

You can find my message under Quick Links, then Private Messages. It should be there.

I don't need the file containing any data, I just want to check how it's built, so that my proposed solution doesn't mess things up for you...

Edit: to moderators: I fully intend to post the code I will write for enxo here on the board, with explanation for his particular case, so please don't close the thread because of the 'behind the board' messaging...

 

[enxocoebl]

hey i have mailed the workbook. the overwriting macro should do the trick. when u open the file a msg pops up abt circular reference. cancel it. u ll find the 'how to' info in the respective tabs. hope this will help u to figure out a solution.

 

[Hermanito]

Okay, got the workbook and have been examining it.

The way it works was totally new for me, I did not know this 'Table' functionality and I will have to look into it, seems interesting.

I have created a solution that should work and involves almost no VBA code. I will mail it to you, so you can try that out, but I will also describe here what I did:

- I created a copy of your Main worksheet and called it MainTemplate, and set it to hidden to avoid confusion.

- I added a code-module (this was not really necessary, but I find it cleaner to do so. You can do this while inside the VBA editor, rightclicking any of your current objects in the tree on the left, your Main worksheet for example, and choosing Insert, Module) and inserted the following code into it:

Public Sub ResetMain()
    If MsgBox("Are you sure you want to reset the invoice?" & vbCrLf & _
              "All entered data will be lost without undo possibility!", vbYesNo + vbExclamation, "Reset Invoice Warning!") = vbYes Then
              
        Sheet1.Cells.Clear
        Sheet4.Cells.Copy Destination:=Sheet1.Range("A1")
    End If
End Sub

The code contains a confirmation dialog to avoid resetting an invoice when you inadvertently click the button (created in next step)... If you don't want this safety feature, just remove the "If..." and "End If" lines.

- Create a button on your Main worksheet and link it to the macro ResetMain. Format the button as you please.

You're ready to go now. You should test on a copy of your file before implementing it for real!
When you need to make changes in your invoice (formula's, layout, whatever), just remember to make them in the MainTemplate worksheet.

Questions or problems? Let me know...

 

[enxocoebl]

hey hermanito,
many thanks. the macro u have used is simple and yet powerful. it does its job very well.
i ve used it on a copy of the main file. the macro i put in the customer tab is making it unstable and excel quits and reopens. so i tried to combine both into one.

Public Sub Resetbilling()
If MsgBox("Are you sure you want to reset the invoice?" & vbCrLf & _
"All entered data will be lost without undo possibility!", vbYesNo + vbExclamation, "Reset Invoice Warning!") = vbYes Then

Sheet1.Cells.Clear
Sheet5.Cells.Copy Destination:=Sheet1.Range("A1")
End If
customer
End Sub

Sub customer()
'
' customer Macro
'

'
Range("Table4[DEBT]").Select
ActiveCell.FormulaR1C1 = ""
Range("Table4[CREDIT]").Select
ActiveCell.FormulaR1C1 = ""
Sheets("BILLING").Select
End Sub

u may notice i ve made one change to ur macro..(sheet4 becomes sheet 5) the rest is a recording.
its still crashing. it worked a coupla times. what i am trying to do with the above recording is .. clear "debt" and "credit" cell in customer tab after running the reset macro.
also there is another problem.. the "bill amount " column in customer tab doesnt work the second time i use the reset button. the problem i think is that reference cell moves vertically down as i enter the products. now after reset.. the formula in the "bill amount" column in customer tab doesnt get reset. but i think thats not a major issue.
can u incorporate the second macro in the first macro? perhaps a proper unification will solve my problem.

 

[Hermanito]

What do you need to happen when you clear the invoice?
Which exact cells need to be cleared and on which sheet are they?
Can there be multiple lines in the customer table at the moment you need to clear it?

You can replace the recorded code simply with code like this:
SomeSheetName.Range("G7:I7").ClearContents

Also, the way you call the customer macro after the End If, will clear your customer data even if you select no in the dialog. I think it's better to move it before the End If.

 

[enxocoebl]

thank you thank you thank you thank you thank you thank you
"You can replace the recorded code simply with code like this:
SomeSheetName.Range("G7:I7").ClearContents

Also, the way you call the customer macro after the End If, will clear your customer data even if you select no in the dialog. I think it's better to move it before the End If."
made both the corrections. works like magic. one click to restore all sheets to its original form. mwahhhhh.... thanx man.

will post the final code tomorrow if it ll help the others. this is a great way to restore worksheets. so happy now

hermanito please dont discard the workbook. theres still a lotta work left for me. youtube videos by excelisfun team helped me a lot. especially excel magic trick 334 and 348.
thank you hermanito.

 

[Hermanito]

hehe, it's nice to get such an enthousiastic thank you

But the VBA code I provided is very very basic... if you're already impressed now, you're in for a world of surprise when you learn more

Good luck with the rest, feel free to ask here if you have problems.

 

[enxocoebl]

the code:

Public Sub Resetbilling()
If MsgBox("Are you sure you want to reset the invoice?" & vbCrLf & _
"All entered data will be lost without undo possibility!", vbYesNo + vbExclamation, "Reset Invoice Warning!") = vbYes Then

Sheet1.Cells.Clear
Sheet5.Cells.Copy Destination:=Sheet1.Range("A1")
customer
End If

End Sub

Sub customer()
Sheet3.Range("H5:I5").ClearContents
Sheets("BILLING").Select
End Sub

i am learning new tricks everyday. thanx to the myriad of videos and experts such as yourself. i am working on the accounting part of it now. and then comes the lookup and update feature i wanna use for the stock. will be posting a question soon until then c ya