+ Reply to Thread
Results 1 to 15 of 15

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

Hybrid View

  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?

  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.

    Private Sub AddUpdateCand_CommandButton_Click()
    Dim idx As Long
    
    Me.Hide 'This keeps the data on the UserForm from disappearing once it's closed down
    
        idx = cboCandidate.ListIndex
    
        If idx = -1 Then
            idx = Worksheets("CandidateData").Range("A" & Rows.Count).End(xlUp).Row + 1 'the following data will be stored on the worksheet called CandidateData
        Else
            idx = idx + 2
            End If
    
            With Worksheets("CandidateData")
                .Range("A" & idx).Value = cboCandidate.Value
                .Range("B" & idx).Value = Cand_ID.Value
                .Range("C" & idx).Value = Cand_EmpStatus.Value
                .Range("D" & idx).Value = Cand_Step.Value
                .Range("E" & idx).Value = Cand_Nep.Value
                .Range("F" & idx).Value = Cand_CityState.Value
                .Range("I" & idx).Value = Format(Cand_PhoneNo.Value, "(###) ###-####")
                .Range("J" & idx).Value = Cand_Email.Value
                .Range("K" & idx).Value = Cand_Resume.Value
                .Range("L" & idx).Value = Cand_Taleo.Value
                .Range("M" & idx).Value = Cand_StatusPS.Value
                .Range("N" & idx).Value = Cand_EligFT.Value
                .Range("O" & idx).Value = Cand_JC.Value
                .Range("P" & idx).Value = Cand_Edpm.Value
                .Range("R" & idx).Value = Cand_Ext.Value
                .Range("S" & idx).Value = Cand_Loc.Value
                .Range("T" & idx).Value = Cand_Shift.Value
                .Range("U" & idx).Value = Cand_PS_ID.Value
                .Range("V" & idx).Value = Cand_Notes.Value
                '.Range("W" & idx).Value = Cand_UseSameItvwTeam.Value
                .Range("X" & idx).Value = Cand_IntA.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("Y" & idx).Value = Cand_IntB.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("Z" & idx).Value = Cand_IntC.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("AA" & idx).Value = Cand_Alt1.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("AB" & idx).Value = Cand_Alt2.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("AC" & idx).Value = Cand_Alt3.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("AA" & idx).Value = Cand_Host1.Value 'Only if answer to UseSameItvwTeam is FALSE
                .Range("AB" & idx).Value = Cand_Host2.Value 'Only if answer to UseSameItvwTeam is FALSE
                
                'If Then Statements
                If YesRelo_OptionButton.Value = True Then
                .Range("G" & idx).Value = "Yes"
                .Range("H" & idx).Value = "Travel/Hotel Authorization Made"
                .Range("AF" & idx).Value = " "
                .Range("AG" & idx).Value = " "
                Else
                .Range("G" & idx).Value = "No"
                .Range("H" & idx).Value = "No Travel Necessary"
                .Range("AF" & idx).Value = "*****"
                .Range("AG" & idx).Value = "*****"
                End If
                
                If NoRelo_OptionButton.Value = True Then
                .Range("G" & idx).Value = "No"
                .Range("H" & idx).Value = "No Travel Necessary"
                .Range("AF" & idx).Value = "*****"
                .Range("AG" & idx).Value = "*****"
                Else
                .Range("G" & idx).Value = "Yes"
                .Range("H" & idx).Value = "Travel/Hotel Authorization Made"
                .Range("AF" & idx).Value = " "
                .Range("AG" & idx).Value = " "
                End If
                
                If Cand_EmpStatus.Value = "Employee" Then
                .Range("L" & idx).Value = "*****"
                .Range("M" & idx).Value = "*****"
                .Range("P" & idx).Value = "*****"
                .Range("Q" & idx).Value = "No Drug Necessary"
                End If
                
                If Cand_EmpStatus.Value = "External" Then
                .Range("R" & idx).Value = "*****"
                .Range("S" & idx).Value = "*****"
                .Range("T" & idx).Value = "*****"
                .Range("U" & idx).Value = "*****"
                .Range("Q" & idx).Value = "Drug Screening"
                End If
                
                If Cand_EmpStatus.Value = "Agency" Then
                .Range("R" & idx).Value = " "
                .Range("S" & idx).Value = " "
                .Range("T" & idx).Value = " "
                .Range("U" & idx).Value = " "
                .Range("Q" & idx).Value = "Drug Screening"
                End If
                
                If Intvw_Type.Value = "Internal/Informal" Then
                .Range("AI" & idx).Value = "*****"
                .Range("AJ" & idx).Value = "*****"
                End If
                
                If Intvw_Loc.Value = Cand_Loc.Value Then
                .Range("AH" & idx).Value = "*****"
                End If
                            
            End With
            
    End Sub

  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
    Me.Hide
    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,644
    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.
    Private Sub cmdClose_Click()
        Unload Me
    End Sub
    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
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
       If CloseMode = vbFormControlMenu Then Me.Hide
    End Sub

  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

    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.

  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

    It has worked. Thank you!

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    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:

    Private Sub UserForm_Initialize()
    Dim varCand As Variant
    
       
        With Worksheets("CandidateData")
            varCand = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
        End With
       
        If IsArray(varCand) Then
              cboCandidate.List = varCand
        Else
               cboCandidate.AddItem varCand
        End If
    
    End Sub
    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,644

    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