Need Store and Retrieve Macro for recalling original value set

Bright01
Bright01 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Why not just copy the entire worksheet used range?

Author

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.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Top Expert 2014

Commented:
You don't have to leave a worksheet to transfer data.

Author

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".
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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

Author

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.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

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.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
TracyVBA Developer

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial