+ Reply to Thread
Results 1 to 8 of 8

Data previously entered on UserForm dissappears, but still stored on worksheet - Excel VBA

Hybrid View

eemiller1997 Data previously entered on... 12-18-2012, 12:50 PM
Leith Ross Re: Data previously entered... 12-18-2012, 02:01 PM
Norie Re: Data previously entered... 12-18-2012, 02:03 PM
eemiller1997 Re: Data previously entered... 12-19-2012, 09:18 AM
eemiller1997 Re: Data previously entered... 12-19-2012, 09:09 AM
Norie That looks about right,... 12-19-2012, 09:32 AM
eemiller1997 Re: Data previously entered... 12-19-2012, 12:24 PM
eemiller1997 Re: Data previously entered... 12-19-2012, 09:58 AM
  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Data previously entered on UserForm dissappears, but still stored on worksheet - Excel VBA

    I feel I've asked this before, but apparently not since I cannot find it. It may have just been part of another question.

    My multi-page userform is set up to store the information entered within two different worksheets within the same workbook; however, at some point - either after the Userform is closed or the Excel workbook is closed, it clears the data from the UserForm. I don't want this to happen, ever. I want it always to pull from the information stored so if the user must close down the workbook and return later to finish, or return a month later to add more information to already exhisting information, they don't have to start completely over. How can I get this Userform to behave that way?

    Thank you!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Data previously entered on UserForm dissappears, but still stored on worksheet - Excel

    Hello eemiller1997,

    The UserForm will not retain data after it has been unloaded from memory. You will have to write the code needed to pull the data from and copy it into the correct controls on the UserForm. Place the code in the UserForm _Initialize event procedure or if there is a lot code, create routines that you can be call in the Initialize event. This will make easier to edit and maintain your code.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

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

    Re: Data previously entered on UserForm dissappears, but still stored on worksheet - Excel

    You need code to populate the userform when it opens.
    If posting code please use code tags, see here.

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

    Re: Data previously entered on UserForm dissappears, but still stored on worksheet - Excel

    According to my googling research, to code the data to populate into the Userform during the initialize event, means reversing the code that puts the code onto the worksheet. Is this correct?

    So, this code:

    Private Sub Enter_CommandButton1_Click()
    
    With Worksheets("JobIntvwData") 'The following data will be stored on the worksheet called JobIntvwData
    
        'Requistion Tab
    
        Sheet4.Range("A2") = Me.ReqRcvd
        Sheet4.Range("B2") = Me.ReqNo
        Sheet4.Range("C2") = Me.RecruiterComboBox
        Sheet4.Range("D2") = Me.HMComboBox
        Sheet4.Range("E2") = Me.Job_Title
        Sheet4.Range("F2") = Me.Relo_Avail
        Sheet4.Range("G2") = Me.JobLocation_ComboBox
        Sheet4.Range("H2") = Me.JC_SG
        Sheet4.Range("I2") = Me.BusinessUnit_ComboBox
        Sheet4.Range("J2") = Me.HM_Called
        
        End With
    
    
    End Sub
    would turn into this code....
        With Worksheets("JobIntvwData") 'The following data will be taken from the worksheet called JobIntvwData
    
        'Requistion Tab
    
        Me.ReqRcvd = Sheet4.Range("A2") 
        Me.ReqNo = Sheet4.Range("B2") 
        Me.RecruiterComboBox =  Sheet4.Range("C2") 
        Me.HMComboBox = Sheet4.Range("D2") 
        Me.Job_Title = Sheet4.Range("E2") 
        Me.Relo_Avail = Sheet4.Range("F2") 
        Me.JobLocation_ComboBox = Sheet4.Range("G2") 
        Me.JC_SG = Sheet4.Range("H2") 
        Me.BusinessUnit_ComboBox = Sheet4.Range("I2") 
        Me.HM_Called = Sheet4.Range("J2") 
        
        End With
    And I would place that code into the into the following Initailize event, just after 'Call PopCandidateCombo'?

    Private Sub UserForm_Initialize() 'These are the lines of code that populates the combobox.
        
        Call PopCandidateCombo
    
    End Sub

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

    Re: Data previously entered on UserForm dissappears, but still stored on worksheet - Excel

    How do I code to pupulate the userform when it opens?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    That looks about right, though you seem to be missing any interviewers.

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

    Re: Data previously entered on UserForm dissappears, but still stored on worksheet - Excel

    This has worked. Thank you very much!

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

    Re: Data previously entered on UserForm dissappears, but still stored on worksheet - Excel

    Yes, I would do the same for that set of data by adding it to the rest of the code above. Great. Thank you. I will test this and get back with you

+ 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