+ Reply to Thread
Results 1 to 6 of 6

Simple Buttons/Macros required to improve efficiency of a process.....

  1. #1
    Registered User
    Join Date
    06-26-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Simple Buttons/Macros required to improve efficiency of a process.....

    Hi, my vb knowledge is very basic, I can look at macros already assembled and understand them, even tweak them and add bits in, but when it comes to starting a macro from scratch (without using the record macro feauture) I'm clueless.

    I have a spreadsheet which would greatly benefit from the addition of 2 buttons (spreadsheet attached).
    Button 1 - I would like this to reduce all balances to zero when pressed.

    Button 2 - I would like this to either add an 'increase' any balances where an increase is present, or subtract a 'withdrawal' from any balance where a withdrawal is present upon a click of the button.

    The conditions are also on the spreadsheet attached.

    Ideally, once the buttons/ macros are created, then any 'CP's' that are added to the spreadsheet (in alpha order so could be any row) could be incorporated in to the macro. Its not essential this part is automated though as new CP's are rare, as long as I understand how the macro works then I can manually add these into the code.

    If anybody could help me out that would be great!

    Many Thanks
    Attached Files Attached Files
    Last edited by Andrew E Smith; 07-05-2012 at 04:44 AM.

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Simple Buttons/Macros required to improve efficiency of a process.....

    I have added 2 buttons - one will change all the values in column F to Zero (0) and the other button will update the balances as per your instructions. Let me know if needs any changes. I have attached a workbook for you to try. Remember that when you run VBA that you cannot undo the changes so make sure it works to your specifications and perhaps do a backup before running on real data.
    PS: You say in the workbook Button 1 should clear all Movements to zero however in your post you said Button 1 should reduce Balances to zero. I followed the instructions in your workbook. In your post you refer to CP's in column B - I am not sure what you wanted with this?
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by smuzoen; 07-03-2012 at 09:14 AM.
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?

  3. #3
    Registered User
    Join Date
    06-26-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Simple Buttons/Macros required to improve efficiency of a process.....

    Thanks very much for your reply, I really appreciate you taking the time to help me out.

    Re button 1 - you have assumed correct, it is the movements that should be reset to zero, apologies for the confusion.

    Re the CP's in column B, please forget I mentioned anything for this.

    I've tried opening your attachment but .xlsm files don't seem to be compatible with my Excel for some reason. However, I have copied the code into my spreadsheet and button 1 works perfectly.

    Re button 2, It is almost working perfectly but I have noticed the following...
    (Please note the L and D balance is determined in column C)
    Increases to the L Balance - the button is subtracting rather than adding
    Withdrawals to the D Balance - the button is adding rather than subtracting

    Also, something which I forgot to mention in my first post (apologies) is that I need a floor of zero on the balances i.e. the balances can never be a negative value.

    Thanks again

  4. #4
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Simple Buttons/Macros required to improve efficiency of a process.....

    I think this should fix it - I neglected to see you were using Excel 2003 so here is a xls version
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-26-2012
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    37

    Re: Simple Buttons/Macros required to improve efficiency of a process.....

    Thanks very much, I really appreciate it. Thats exactly what I wanted.

  6. #6
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Simple Buttons/Macros required to improve efficiency of a process.....

    Fantastic. Can you change this thread to solved. If you need any further help just ask

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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