+ Reply to Thread
Results 1 to 12 of 12

Choose from different balances in VBA

  1. #1
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Choose from different balances in VBA

    Hello All,

    What is the best way to code the following

    textbox1 shows the remaining hours of box1 [i.e. 48]
    textbox2 shows the remaining hours of box2 [i.e. 32]
    textbox3 shows the remaining hours of box3 [i.e. 8.5]

    The user can put a number of hours in textbox4 that need to be extracted from the above boxes.

    First box1 needs to be empties then 2 then 3.

    So if the user puts 100 in textbox4 the new values will be as follows:

    textbox1 shows the remaining hours of box1 [i.e. 0]
    textbox2 shows the remaining hours of box2 [i.e. 0]
    textbox3 shows the remaining hours of box3 [i.e. -11.5]

    if the user puts 50 in textbox4 the new values will be as follows:

    textbox1 shows the remaining hours of box1 [i.e. 0]
    textbox2 shows the remaining hours of box2 [i.e. 30]
    textbox3 shows the remaining hours of box3 [i.e. 8,5]

    etc.

    the 3 new values will be stored on sheet1 in column K below each other
    Sheets("sheet1").Range("K" & LastRow).Value =
    Sheets("sheet1").Range("K" & LastRow).Value =
    Sheets("sheet1").Range("K" & LastRow).Value =

    Any ideas?
    Last edited by Jonathan78; 05-01-2012 at 03:42 PM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,523

    Re: Choose from different balances in VBA

    It would be best to supply a sample workbook showing what your layout looks like.

  3. #3
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Choose from different balances in VBA

    Hello Dave,

    This is an example workbook

    Book1.xlsm

  4. #4
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Choose from different balances in VBA

    anyone????

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Choose from different balances in VBA

    Hi Jonathan
    This code appears to work with your sample data
    Please Login or Register  to view this content.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  6. #6
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Choose from different balances in VBA

    Hello John,

    Thanks! it works for both samples I gave in my first post.
    It does not work correctly however when I enter an amount less then the amount given in box1.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Choose from different balances in VBA

    Hi Jonathan
    In your sample, what would you expect Textbox1, TextBox2 and TextBox3 to show if you entered 30 in TextBox4?

  8. #8
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Choose from different balances in VBA

    The result should be as follows:

    textbox1 shows the remaining hours of box1 [i.e. 18]
    textbox2 shows the remaining hours of box2 [i.e. 32]
    textbox3 shows the remaining hours of box3 [i.e. 8,5]

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Choose from different balances in VBA

    Hi Jonathan

    Hold off on that...needs a small correction!!!

    Add these few lines of code
    Please Login or Register  to view this content.
    Last edited by jaslake; 05-01-2012 at 11:22 AM.

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Choose from different balances in VBA

    Hi Jonathan

    Try this instead
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    08-19-2009
    Location
    Netherlands, Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    585

    Re: Choose from different balances in VBA

    That's it, thank you John!

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Choose from different balances in VBA

    Hi Jonathan

    You're welcome...glad to be of help. If that satisfies your requirement, please mark your Thread as solved.

+ 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