Need Store and Retrieve Macro for recalling original value set

EE Pros,

I am looking for help with macros for a workbook I'm working on that simply takes a particular range name that is selected and posts it to another Data Store Tab.  Changes can then be made to the original data set and either be overwritten by the original data set (now on the Data Store Tab) or can be stored and overwrite the original data on the Data Store Tab.  

Attached is a mockup Workbook for your review.

Thank  you in advance for the help!

Bright01
Store-and-Retrieve-Macro.xlsx
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Why not just copy the entire worksheet used range?
0
Bright01Author Commented:
Great question.  Presently, what I have is a formula based model that produces certain results.  I would like to have a save/restore system that only deals with a single column (identified as a named range) being able to move it back and forth in a data store without having to leave the WS for another WS.  I plan to store 50 to 100 different columns so it would be complicated from a user perspective to restore a WS vs. simply selecting a particular stored rangename or column selection.

Make sense?

B.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Can I ask what the underlying purpose of this is?  It feels sort of like you want to be able to do some "what if" sort of work, and if the changes made don't work out you want to be able to undo them?

If that is the case I would think about making your "save" chronological in nature, rather that a block of the sheets data.  It feels like it would be easy when you have many of these saved "chunks" of the sheet to mix and match them wrong and get the data out of balance.  Whereas if you were able to take a "snapshot" of the whole sheet and label it with a comment and time stamp, then if you need to return to it you have a cohesive data-set that worked up to that point.

The saves could be the whole workbook to files with special names, or just new hidden sheets arranged by date.

I may be completely missing your use case, but just wanted to pass this thought along.


»bp
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

aikimarkCommented:
You don't have to leave a worksheet to transfer data.
0
Bright01Author Commented:
Bill,

To answer your question;  This is a program for inputing 12 values (basecase), doing some modeling on those values (what ifs) which create a current case.   By storing the defined rangenames, I hope to be able to retrieve a basecase and re-model the data, again, storing it for reuse.  I had hoped in my question/example, I would get a chance to see how the code may be structured to store/retrieve data without leaving the primary WS.  There are a number of additional aspects to this simple routine such as an alert before overlaying existing "same name" data.  As with many EE questions, they build on themselves as a novice such as myself learns from what the Experts demonstrate.  I typically author 3 to 5 questions on the same routine / result I'm trying to accomplish and it builds both the capability and my personal knowledge of VBA.

Hope that helps with regard to the intent of the "ask".
0
byundtMechanical EngineerCommented:
As an alternative to a dropdown, I listed the named ranges and put checkboxes next to them. If the box is checked, a command button will save or restore the values using the same addresses on Financial_Calcs and Data_Store worksheets.
'Goes in code pane for worksheet containing checkboxes
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Adds a Forms toolbar checkbox to the cell being double-clicked
'Takes its caption from the value of the cell before it was double-clicked
'Checkbox value is linked to that same cell, but hidden by Custom number format ;;;
With ActiveSheet.CheckBoxes.Add(Target.Left + Target.Width - 10, Target.Top - 1, 10, Target.Height - 1)
        .Caption = ""
        .LinkedCell = Target.Address
        .Name = "cb" & Target.Address(False, False)     'Name it like "cbA23"
End With
Target.NumberFormat = ";;;" 'Make TRUE or FALSE value invisible
Cancel = True
End Sub

Open in new window


The code for the save and restore is shown below.
Sub StoreUsecaseParameters()
'Named ranges are stored in same cells on worksheets Financial_Calcs and Data_Store
Dim ar As Range, cel As Range
With Worksheets("Financial_Calcs")
    For Each cel In .Range("RestoreNames").Cells
        If cel.Offset(0, -1).Value = True Then
            For Each ar In .Range(cel.Value).Areas      'Assumes the named ranges are all on Financial_Calcs worksheet!
                Worksheets("Data_Store").Range(ar.Address).Value = ar.Value
            Next
        End If
    Next
End With
End Sub

Sub RestoreUsecaseParameters()
'Named ranges are stored in same cells on worksheets Financial_Calcs and Data_Store
 Dim ar As Range, cel As Range
With Worksheets("Financial_Calcs")
    For Each cel In .Range("RestoreNames").Cells
        If cel.Offset(0, -1).Value = True Then
            For Each ar In .Range(cel.Value).Areas      'Assumes the named ranges are all on Financial_Calcs worksheet!
                ar.Value = Worksheets("Data_Store").Range(ar.Address).Value
            Next
        End If
    Next
End With
End Sub

Open in new window

Store-and-Retrieve-MacroQ29067022.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Brad,

Thank you very much for the effort on this.  My mother-in-law has been in Rehab for the past week and it has consumed most of my time.  I will look at this today.  Again, and as always, thank you for the response and I'll be back  on this shortly.
0
byundtMechanical EngineerCommented:
Jim,
After my post, I thought about whether you might prefer displaying a userform to select the variables to be stored/retrieved. The userform could use checkboxes in a three column list (checkbox, range name, description), with the latter two taken from a Data worksheet. Or it could have a multi-select listbox control more like your original request with that same information.

I suggest against hard-coding the range names (and descriptions) in VBA code. Doing so would make the code harder for you to maintain.

Brad
0
Bright01Author Commented:
Brad,  This is a good first step for what I'm trying to do with Store and Retrieve.  I'll build on it and submit a related question in a few days.  I'm still at the Rehab. Center with my Mother-In-Law but should have some time to  work on it later this week.

Will also consider a User Form but since there are more then 3 columns, will  probably prefer a rangename drop down list since it will be ever expanding as things are saved.  The checkbox would work if there were a limited number of cases to retrieve but I need something similar to what you provided several months ago with the Workbench and selecting a "Demo".

As always, "thank you"!!!

B.
0
byundtMechanical EngineerCommented:
Jim,
A data validation dropdown allows just one selection of a range name.

A forms control listbox or combobox allows multiple selections--but is ugly when you plop it on top of a range of cells. That's why I was suggesting a userform.

Brad
0
TracyVBA DeveloperCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: byundt (https:#a42359686)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.