+ Reply to Thread
Results 1 to 10 of 10

Cell Reset?

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Sussex, England
    MS-Off Ver
    Excel For Mac 2011
    Posts
    32

    Cell Reset?

    I am working on a business spreadsheet, and I was wondering, is there a way that selected cells can be programmed to reset at certain intervals?

    So on a particular form, there are details of order numbers etc, but, would it be possible for excel to automatically reset the order numbers on a monthly basis?

    I am using Excel for Mac 2011

    Thanks in advance for your help.
    Last edited by diggers16; 12-26-2012 at 10:32 AM.

  2. #2
    Forum Contributor
    Join Date
    10-31-2010
    Location
    london
    MS-Off Ver
    Excel 365
    Posts
    150

    Re: Cell Reset?

    Yes, but I think you would need to use VBA. You could use some code to activate on the sheet open event which checks the date and resets the cell accordingly.

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Cell Reset?

    I agree with bopsgtir. To do this requires macros. The code is not complicated, although you have to reserve someplace to record when you did the reset so you do exactly one reset each month. Here is what I would suggest. Note that I have no access to a Mac and am assuming that macros work the same way as they do in Office for Windows.

    Create a new worksheet, name it "Last Reset", and then hide it. The user never has to see it.

    The following code goes into the module called ThisWorkbook*. You haven't given any details about your data layout or what you want to reset so I can't give you a specific answer:
    Please Login or Register  to view this content.
    *To install this code, open your Excel file then press ALT-F11. This will open the VBA development window. On the left, you will see a tree of open Excel files, and under each one a list of worksheets. Find your file, and then underneath find ThisWorkbook. Double click on it. On the right side you will see the window for the code for ThisWorbook. Copy and paste this code. I do not know if Mac 2011 uses the XML format. If it does, Excel files are normally named .xlsx. You must save this file as a macro-enabled file, with an extension of .xlsm.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    12-24-2012
    Location
    Sussex, England
    MS-Off Ver
    Excel For Mac 2011
    Posts
    32

    Re: Cell Reset?

    Thank you both for your help.

    I have attached a screen shot of the sheet I am talking about, and all I want to do, is to be able to reset the values of 'Quantity Ordered' and 'Quantity Sold' columns on the first of every month.

    Is this possible to do, using your code?

    Thank you very much again, for your help.
    Attached Images Attached Images

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Cell Reset?

    Yes but what does "reset the values" mean? Do you mean "set all values to zero"?

    Also please see the last line of my signature.

  6. #6
    Registered User
    Join Date
    12-24-2012
    Location
    Sussex, England
    MS-Off Ver
    Excel For Mac 2011
    Posts
    32

    Re: Cell Reset?

    Yes, I mean reset them to 0.

    And apologies, I'm sorry, I have attached the whole workbook, but I'm focussing on the "Product Orders & Stock" sheet.

    Thank you for your help with this.
    Attached Files Attached Files

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Cell Reset?

    Here is the final product. I also corrected the typo on the column G header
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-24-2012
    Location
    Sussex, England
    MS-Off Ver
    Excel For Mac 2011
    Posts
    32

    Re: Cell Reset?

    Thank you so much for doing that. Hahaha, I didn't notice the header typo there

    I guess I will find out if everything works tonight So on the hidden sheet called 'Last Reset', there is a date on there. Is this date basically the date that resets the data every month. So as the date is currently 26/12/2012 on there, does this mean that the next reset will be on 26/1/2013? So if I change the date to 27/11/2012, it should reset itself tonight? Also, does this last reset date change itself every month, so I don't have to go in and change it every month?

    Also, did you use macros to do this? As no macros are coming up on my list of macros for the spreadsheet.

    Thanks Again!

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Cell Reset?

    Let me answer the last question first. Yes, it uses macros. Macros are required to do this. Post #3 above explains how to install the code, and you can use the same steps to view it. This code is in a module called ThisWorkbook, and is not visible when you look at available macros from the Excel interface.

    When the workbook opens, the code runs and it checks the date in Last Reset. If that cell is empty, or if the date is from the previous month, then the code knows that a reset has not been performed in the current month. In that case it performs the reset, and then updates the date to be the current date. This way the reset is only performed the first time you open the workbook each month.

    To answer your questions, the date in "Last Reset" is the most recent time the reset was performed. It is set by the code. You ran the code and did a reset today, so the next reset will occur the first time you open the file in January (which may be 26/1/2013, but also could be any other day in January).

    If you manually change the date to 27/11/2012, yes it will force a reset the next time you open the file. Normally you never have to touch this, it's all automatic. Remember that the code will only run when you actually open the file, so it won't reset itself tonight--it will reset the next time you open the file. In general VBA code can only run when the file is open. This particular macro will only run at the moment the file is first opened.

    "Last Reset" is hidden but there is no technical reason it has to be. You can leave it visible if you want. If someone else uses your file it might be confusing but that's up to you.

  10. #10
    Registered User
    Join Date
    12-24-2012
    Location
    Sussex, England
    MS-Off Ver
    Excel For Mac 2011
    Posts
    32

    Re: Cell Reset?

    Ok, I understand it now.

    Thank you for all of your time and effort you have invested in me. I really appreciate it, Thank You!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1