+ Reply to Thread
Results 1 to 15 of 15

Code a Command Button on UserForm not to close upon click - Excel VBA

  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Code a Command Button on UserForm not to close upon click - Excel VBA

    How can a button coded to send information to a worksheet in the excel document be coded so it doesn't close upon using it? I have a button I'm calling 'Enter' and another that is called 'Add/Update' and both of them send information to their respective worksheets within the excel workbook, but when they are used they close the UserForm. Since, the UserForm is designed for the user never to have a need to necessarily close the Userform, I don't want that to happen. I want the user to choose when and if they will close the UserForm, and then simply use the X to do so.
    Last edited by eemiller1997; 12-14-2012 at 09:17 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    The codes being executed must include instructions for the userform to close for that to happen. Remove those instructions.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor ptm0412's Avatar
    Join Date
    04-16-2008
    Location
    Vietnam
    MS-Off Ver
    Office 2003 and 2007
    Posts
    129

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    Could you tell me what kind of information that it send to sheet? Where is the button placed on, a real userform? And the userform closed?
    May I see the code?
    Oldman Chatting: [email protected] Mailing: [email protected]

  4. #4
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    I'm not sure what part of the code tells the UserForm to close upon click.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    remove the
    Please Login or Register  to view this content.
    line
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    The reason I was told to put that there was so when then it does close, it doesn't clear all of the data entered into the UserForm. Is that the only reason it does close?

    Where should I put that code elsewhere so when the user clicks the X, the data remains in the UserForm?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Me.Hide doesn't close the userform it hides it.

    You don't need that code anywhere.

    To close the form you need to add another button outside the frame.

    Then you can use this code to close the form.
    Please Login or Register  to view this content.
    Last edited by Norie; 12-14-2012 at 10:08 AM.
    If posting code please use code tags, see here.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    if you want it to hide and not unload put it in the userform's queryclose event
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    I will give that a try. Thank you.

  10. #10
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    It has worked. Thank you!

  11. #11
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    I Think. Let me get back to you. It doesn't close down now when I click different command buttons, however, two of my tabs on my Multi-Page UserForm did not have their information in them when I re-opened the UserForm.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Use the userform Initialize event to populate the 2 tabs.

  13. #13
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    How do I do that, Norie? Would I duplicate what I already have in there as follows:

    Please Login or Register  to view this content.
    Or, is there something else?

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    If the 2 pages are the ones I think they are it's the job/interview data you need to load not candidate data.

    The code would go in their but it would be pretty different.

  15. #15
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Code a Command Button on UserForm not to close upon click - Excel VBA

    Yes, there is a worksheet called 'JobIntvwData' and two tabs, one called 'RequisitionTab' and 'TeamTab'.

+ 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