+ Reply to Thread
Results 1 to 12 of 12

If Cancel on Input Box, exit sub

  1. #1
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    If Cancel on Input Box, exit sub

    I'm trying to adjust this macro stop that the macro stops if the user hits cancel.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: If Cancel on Input Box, exit sub

    I must be missing something. If I click Cancel the InputBox disappears and there is no change in J63.

    What are you seeing?

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: If Cancel on Input Box, exit sub

    Hi,

    Try this:
    Please Login or Register  to view this content.
    What you have will work, however if you have some more code after the If...Then statement then the Exit Sub is required as above to stop it from executing

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: If Cancel on Input Box, exit sub

    Well, you haven't really got anything to stop it from but

    Please Login or Register  to view this content.

    This will stop if the presses Enter with an empty box, selects Cancel or presses Escape. Otherwise it will put the value in the cell and display the message.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: If Cancel on Input Box, exit sub

    I'm attaching a sample, it's kind of hard to explain. When the user clicks on the dropdown in the ribbon and clicks cancel, I don't wan't the image or any other data to show.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: If Cancel on Input Box, exit sub

    There are only 4 drop-downs in your Ribbon, and none of them have a Cancel option...can you please elaborate for us?

  7. #7
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: If Cancel on Input Box, exit sub

    It's setup to only be accessed by the windows user name in the cuiZ_DataSource Module
    Please Login or Register  to view this content.
    Last edited by guitarsweety; 07-28-2014 at 07:03 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: If Cancel on Input Box, exit sub

    But why do you want a Cancel button? If the user clicks on one of the drop-downs and wants to Cancel, then all they have to do is click off the list somewhere...

    Nonetheless, I have changed the Month_onAction subroutine to do what I think you are trying to achieve if the user selects "NA" from the drop-down (NB: I have only changed the Human Resource drop-down, not the other 3).

    Let me know if this helps
    Attached Files Attached Files

  9. #9
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: If Cancel on Input Box, exit sub

    Oh my goodness ajryan88, this works exactly like I want it too and I can definitely adjust the others, thank you so much for your help with this. I'm jumping up and down and can get back to my crochet

  10. #10
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: If Cancel on Input Box, exit sub

    No worries. The only thing I should mention (just in case you missed the subtlety), is that this won't work if more names are added to the list, as the index refers to the position of the selected item on the list. So just make sure you change the value of index in the If...Then condition if you add to the list.

    Also, please don't forget to mark this thread as solved (Thread Tools --> Mark As Solved), and please click on the * next to my post to say thanks

  11. #11
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: If Cancel on Input Box, exit sub

    Thank you for bringing that to my attention. I'll look more into the index. Thank you again. I love learning something new.

  12. #12
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: If Cancel on Input Box, exit sub

    Oh wait one more thing I just saw this
    Please Login or Register  to view this content.
    If I have say 3 do I just change to say
    Please Login or Register  to view this content.

+ 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. When Cancel is selected, I want to exit sub
    By Rubert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2014, 05:02 PM
  2. [SOLVED] Input Box - Exit Sub on Cancel
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2013, 11:32 AM
  3. tidy way to exit sub on cancel
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-19-2009, 04:44 AM
  4. Exit Sub when I hit cancel
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2007, 01:27 PM
  5. Cancel an application exit from VBA?
    By helmekki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2005, 10:31 PM

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