Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Macro to ease saving/invoking Excel scenarios (Excel 2002)

    Sorry about the length of this question; I

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

    Re: Macro to ease saving/invoking Excel scenarios (Excel 2002)

    If you want to store the "states" in the workbook itself, you could do the following:
    <UL><LI>Insert a new worksheet into the workbook.
    <LI>Make it hidden (or if you prefer, set its Visible property to xlSheetVeryHidden in the Visual Basic Editor).
    <LI>Create a userform with
    <UL><LI>A list box or combo box to list the existing "states".
    <LI>A text box to enter a new name in.
    <LI>Command buttons for Load, Save, Add, Delete and Close.[/list]<LI>When the user enters a name and clicks Add:
    <UL><LI>The code determines the first 10 x 5 block of free cells in the hidden sheet.
    <LI>It names this block using the name provided by the user.
    <LI>It copies/pastes B3:F12 to the named range.
    <LI>It adds the name at the bottom of a list elsewhere in the hidden sheet, and adjusts the row source of the list box/combo box.[/list]<LI>When the user selects a name in the list box/combo box and clicks Load:
    <UL><LI>The code copies/pastes the range of that name into B3:F12 (perhaps paste special with the Values option)[/list]<LI>The Save button overwrites an existing named range with the current values.
    <LI>The Delete button deletes the named range, removes the name from the list and updates the row source of the list box/combo box.[/list]This is just the basic idea. You'll need to add a lot of error checking.

    Another possibility would be to store each "state" in a separate small workbook. This makes it easy to exchange "states" with other users.
    You can use the GetOpenFileName and GetSaveAsFileName dialogs to let the user open/save states.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Macro to ease saving/invoking Excel scenarios (Excel 2002)

    Hans,

    This is a very useful starting point. Thank you for taking the time to outline the "baby steps" -- I'm now encouraged to try this out.

    Your answer, by the way, is much closer to what I was looking for than the full code which Jezza suggested I was seeking.

    Regards, and thanks again,

    Lingyai

Posting Permissions

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