Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Save a value for use later (Excel xp)

    I am making a user form for them to enter ranges in excel. I'm making a recall command button so they don't have to retype in the ranges the next time they open the form. I don't know how to define the variable so it won't be discarded when the module closes or the uses exits from the macro. I tried const but that doesn't seem to work. Is it possible to save a value and recall it even after the user has exited from the macro? thank you

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Save a value for use later (Excel xp)

    Why don't you save the value as a string in a named cell, then recall the cell value?

    zeddy

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save a value for use later (Excel xp)

    Why not save the value(s) to an unused range and just read it back next time the form is loaded. You could also save the values to a file or indeed the registry, but using excel's own cells seems the best approach. You could hide the values away.

    Andrew C

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save a value for use later (Excel xp)

    oh, yes that will work just fine. thank you very much

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a value for use later (Excel xp)

    You should declare the variables that are to hold the old ranges at the top of a normal module (called the declaration section):

    'Top of module
    Public sOldRange1 as String
    Public sOldRange2 as String
    Public sOldRange3 as String

    'Below this the first Sub or Function

    Now these variables will retain their value until you reset your project or press End at a runtime error.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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