Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Nov 2007
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Keeping form value (vba)

    Hi,
    I have a program that will do some calculation for me.
    I put in the information that i want to be calculated in the form and click ok
    then it would calculate and spit the answer into excel.

    I wants to keep the values from the previous calculation information in the form.
    How can i do that little trick ?

    Please Advise. Thank you

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Keeping form value (vba)

    What kind of program is this? Is it a third-party application or something you're developing yourself?

    And where do you want to store the values? In Excel (if so, where) or somewhere else?

  3. #3
    Lounger
    Join Date
    Nov 2007
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping form value (vba)

    Its Excel Vba form,
    when i do my first calculation, input form will be prompted
    i would like the value from the first calculation to stay in the form for reference, instead of being empty.
    Thank you

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Keeping form value (vba)

    If you close the userform using Unload Me, all values entered by the user will be cleared. But if you hide it using Me.Hide, the controls will retain their values for the next time you show the form using code similar to Userform1.Show. Does that help?

  5. #5
    Lounger
    Join Date
    Nov 2007
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping form value (vba)

    Thanks Hans,
    I will try it out now.

  6. #6
    Lounger
    Join Date
    Nov 2007
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping form value (vba)

    Hi Hans,
    I tried out the method that you have provided, but no the values in the form still would not stay.
    Do you have any other way?
    thanks

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Keeping form value (vba)

    The easiest way would be to store the values in some cells in a worksheet and then retrieve them in the Initialize event of the userform, I think.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Lounger
    Join Date
    Nov 2007
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping form value (vba)

    Ya... that is a good idea.. ill try it
    thanks

  9. #9
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Keeping form value (vba)

    I was surprised Hans's suggestion didn't work for you, so I tested it and it works for me (in Word anyway; I'm not an Excel user). If you want to put a little more time into trying to get Hans's approach to work, you could try putting MsgBox "Form is unloading ..." in the UserForm_Terminate event for the form. I suspect that you'll see the MsgBox in between calculations, which will mean that you're not just hiding the form, you're unloading it -- which in turn will mean (I think) that there must be some code somewhere that you haven't adjusted yet that is unloading the form (rather than just hiding it).

    On the other hand, if the form is unloading because it's located in a particular workbook and you're closing that workbook in between calculations, then ignore this post.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Keeping form value (vba).

    It's also just possible that there is an error occurring, that is being handled silently, and that the <code>userform1.show</code> syntax is actually creating a new copy of the form. Easily tested using a variable to hold the reference to the form (which I think is better for a serious application anyway):
    <pre>Dim frm As Userform1
    Sub ShowForm()
    Set frm = New Userform1
    frm.Show
    End Sub
    </pre>

    for example.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •