+ Reply to Thread
Results 1 to 19 of 19

Confirmation pop-up on cell value change

  1. #1
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Confirmation pop-up on cell value change

    Hello, all! I need help with getting a confirmation pop-up message with Yes and No buttons that confirms user's intention to change cell's value on the sheet. In my project, this pop-up message should only work in columns D, E, and F. The reason for the pop-up is to prevent accidental change of data in these columns.
    Last edited by chrisneu; 10-15-2009 at 01:58 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Confirmation pop-up on cell value change

    I think is is going to get pretty annoying for the user, but here is one way to go about it - modify as required.

    The code needs to go into the sheet module.

    Please Login or Register  to view this content.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Confirmation pop-up on cell value change

    Try this
    Please Login or Register  to view this content.
    Copy the code
    Select the worksheet in which you the code to run
    Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
    Where the cursor is flashing, choose Edit | Paste
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Re: Confirmation pop-up on cell value change

    Thank you very much, Palmetto. This is very close to what I want, but I do not want this message to pop up if I just double-clicked the cell but made no changes and if the change was made and I click "No" to accept the change, your code generates a message advising to correct the entry as required. Is it possible that it just puts back the old value?

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Confirmation pop-up on cell value change

    Thank you very much, Palmetto. This is very close to what I want, but I do not want this message to pop up if I just double-clicked the cell but made no changes and if the change was made and I click "No" to accept the change, your code generates a message advising to correct the entry as required. Is it possible that it just puts back the old value?
    The code is RoyUK's post is closer to what you want and should do the job. Give it a try and if need further refinement, post back.

  6. #6
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Re: Confirmation pop-up on cell value change

    Thank you very much, royUK. It worked with the first-cell try, but then it stopped working for some reason. I saved the file, re-pasted the code, re-opened the file... it still does not work. The sheet and cells act just like they would without the code.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Confirmation pop-up on cell value change

    Fixed it
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Re: Confirmation pop-up on cell value change

    Thank you very much, RoyUK. The pop-up works now, but it appears that buttons Yes and No have the same function, which is to accept the change.

    Also, if I just double-click a cell but then leave the cell withouth making any changes, I don't want the message to pop-up.

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Confirmation pop-up on cell value change

    This should work
    Please Login or Register  to view this content.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Confirmation pop-up on cell value change

    Here's another way ...
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Re: Confirmation pop-up on cell value change

    Thanks a lot, RoyUK! That is exactly what I wanted and it works great!

    One last question: when the pop-up message refers to the cell address, like $E$4, is going to be too much effort to eliminate the "$" character from that message, leaving only "E4"?

  12. #12
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Re: Confirmation pop-up on cell value change

    Thank you, SHG... this one works great too! I apreciate it!

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Confirmation pop-up on cell value change

    remove the $ with subtitut
    Please Login or Register  to view this content.

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Confirmation pop-up on cell value change

    Or

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    10-15-2009
    Location
    Austin, TX - USA
    MS-Off Ver
    Microsoft 365
    Posts
    114

    Re: Confirmation pop-up on cell value change

    Thank you so much to everyone! You, guys, are great!

  16. #16
    Registered User
    Join Date
    09-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Confirmation pop-up on cell value change

    Hi fellas. Long time reader, first time poster.

    Bumping up an old thread here.

    @royUK
    How do I go about getting this to work only on cells that are populated? As it stands right now, this fires on every cell edit including the empty ones.

    Also, if it's not too much trouble, I'd like something similar for "Worksheet_SelectionChange" where

    Condition: if the cell is populated and a user clicks on it,
    Options: it throws out a box with 2 options "Yes" and "No"
    Yes = the cell is selected
    No = Select the first empty cell in Column B

    BTW - You guys are awesome. Cheers.

  17. #17
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Confirmation pop-up on cell value change

    LEEPRIESTENATOR,

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  18. #18
    Registered User
    Join Date
    09-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Confirmation pop-up on cell value change

    Oh man, I'm so sorry. I knew I should have read the rules before I posted. Was in a hurry to get this over as I've been working non stop for 2 days.

    I'll read up on the rules right away and make the changes.

    Thanks *thumbsup*

  19. #19
    Registered User
    Join Date
    12-14-2017
    Location
    Italy
    MS-Off Ver
    2016
    Posts
    8

    Re: Confirmation pop-up on cell value change

    Hi all,
    this piece of code works great thanks a lot.
    However, what the original cell is a formula? any idea how to reset it when you say "no" to the message box? actually the code substitutes the formula with its value.

+ 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