+ Reply to Thread
Results 1 to 11 of 11

VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    SLC, UT
    MS-Off Ver
    365
    Posts
    5

    VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    I am creating a salary distribution spreadsheet where I need to be able to enter either a percentage into cell 1 or a dollar amount in cell 2 and have both cell values always equal each other.

    The cell value, whether percentage or dollar amount, is a fraction of a total salary.

    As an example, if a salary equals $100, I want to be able to determine either what the dollar amount of 25% of the salary is or what the percentage of $25 of the salary is. This is because sometimes I will know only the percentage, but other times I will know only the dollar amount.

    So I want both cell 1 and cell 2 to equal each other, both be input cells, and be expressed in different ways.

    (In this example where salary equals $100, I'd need cell 1 to equal 25% and cell 2 to equal $25, no matter which of the two cells I used to enter either 25%/$25.)

    So far I have found that this code will get the cells to equal each other, but it does not allow for the cells values to be expressed in the different ways mentioned above.

    Please Login or Register  to view this content.
    Any advice would be greatly appreciated.
    Last edited by AliGW; 07-16-2019 at 12:34 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this requirement is not optional. As you are new here I have done it for you this time.)
    Last edited by AliGW; 07-16-2019 at 12:54 PM. Reason: Wrong canned message - now corrected.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-16-2019
    Location
    SLC, UT
    MS-Off Ver
    365
    Posts
    5

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Wow. The moderation on this forum is terrible; the only reason I have posted this question here is because I could not find this question anywhere. There was a similar question on this forum (cell 1 = cell 2 & cell 2 = cell 1) but every new post that was getting added to that thread, that was a related but different question - as mine is - was told to start a new thread as it was a different question. So that's what I did.

    And perhaps I'm blind but I don't see that the moderator in this case added a link to a cross post as they said they did. If only they had been able to do that, my question could perhaps be answered.

    Any advice would still be greatly appreciated, and I'll keep my eye out for it, but after that I'm going to leave this forum and never return.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Oops - the canned message is the wrong one - I do apologise! It was meant to be about code tags, which I HAVE added for you. I will amend my post above.

    Actually, the moderation on this forum is, usually, extremely good. However, the moderators are human ...

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    jimmyw3,

    It is too bad you are so upset over the moderation error, as AliGW said they are human.
    However, I won't make a scene over it... I came to try and help. My question is this... how will the code know what the salary is? To express a number as a %, or calculate the result of a % input, we need to know the salary itself of course. Should that be a variable in the code? Or is it stored in another cell?

    Attaching a sample workbook would be useful, but alternatively give me a quick explanation of how you enter the %... is it a decimal (0.25) or do you just enter 25 and the cell is formatted as % ?
    Last edited by Arkadi; 07-16-2019 at 01:28 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  6. #6
    Registered User
    Join Date
    07-16-2019
    Location
    SLC, UT
    MS-Off Ver
    365
    Posts
    5

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Though you brought it up again, my opinion of the moderation/rules here hasn't changed. If you're able to help though I am grateful for it.

    The salary is stored in another cell on the same worksheet. The code should pull the salary from the cell it is stored in.

    The % cell is formatted to convert input into a %, and the $ cell is formatted to convert input into $.

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    How about:

    Please Login or Register  to view this content.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Though you brought it up again, my opinion of the moderation/rules here hasn't changed.
    I feel very sorry that you feel unable to accept my apology, my admission of guilt, my explanation of what had happened and my correction of the error.

    You are, of course, entitled to your opinion about me, however I would urge you not to tar the rest of the moderation team with the same brush or be dismissive of our rules, which are pretty straightforward and designed to keep the forums running smoothly.

    Once again, I am sorry that I accidentally posted the wrong canned message - the one in the list that sits above the one I meant to use.

    It would be a mistake to turn your back on these forums. The help and advice offered here really is second to none.

  9. #9
    Registered User
    Join Date
    07-16-2019
    Location
    SLC, UT
    MS-Off Ver
    365
    Posts
    5

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Arkadi, that code worked flawlessly. I can't thank you enough.

    AliGW - The advice from the excel experts on this forum is outstanding, such as what I just received from Arkadi. Please understand that I am not trying to suggest that you as a person are bad, or that your human error is unacceptable. I simply have a negative opinion of the way the moderation rules are enforced; Too many threads I scroll through seem to be so populated by moderator posts (stemming from all too easily broken rules) that it detracts from the experts' posts. That is my negative opinion - of the system, not you as a person or any of the other moderators as a person. (Further example: this post has some great content but our little back and forth has detracted from it.) Thanks!

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    @jimmyw3, I'm glad that works as you wanted it to Appreciate the rep also.
    If I could ask a favour, could you mark the thread as solved? It lets others know that it is resolved, and they don't have to open it to find out. Thanks in advance.

  11. #11
    Registered User
    Join Date
    07-16-2019
    Location
    SLC, UT
    MS-Off Ver
    365
    Posts
    5

    Re: VBA Worksheet Change Event - Cell 1 (%) = Cell 2 ($) & Cell 2 ($) = Cell 1 (%)

    Thread marked as solved. Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Worksheet change event with multiple column cell value change
    By borgyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2015, 11:53 AM
  2. Worksheet Change Event based on value of a cell
    By ron2k_1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2014, 01:01 PM
  3. [SOLVED] worksheet change event only when certain cell changes
    By Thirsa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-06-2013, 07:09 AM
  4. Worksheet change event not responding to cell deletion
    By jman0707 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-14-2008, 06:30 PM
  5. Worksheet change event not responding to cell deletion
    By jman0707 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2008, 03:22 PM
  6. Cell value change to trigger macro (worksheet change event?)
    By Neil Goldwasser in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2006, 10:00 AM
  7. [SOLVED] change event on specific cell rather than worksheet
    By frendabrenda1 in forum Excel General
    Replies: 3
    Last Post: 09-20-2005, 10:05 AM

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