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?
 
byundtCommented:
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Bill PrewCommented:
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
 
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
 
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
 
byundtCommented:
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
 
byundtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.