Bernie,

"Bernie Deitrick" wrote:

Dan,

For simplicity and maintainability, move all your data links into one table, on a sheet named
"DataLinks".

Let's say that you have the links starting in cell A2, down to A21. In B2, use the formula

=A2

and copy that down to B21. Now you will have a 'doubled' table of data.

Here's the key point - Link all of your cells needing the values from the data links to the cells in
column B.

When you want to freeze the values, run this macro:

Sub Freeze()
Worksheets("DataLinks").Range("B2:B21").Value = _
Worksheets("DataLinks").Range("B2:B21").Value
End Sub

When you want to unfreeze the links, run this macro:

Sub UnFreeze()
Worksheets("DataLinks").Range("B2:B21").FormulaR1C 1= _
"=RC[-1]"
End Sub

The links in column A are unaffected by these manipulations.

......
the macros work as a charm. Thanx. That's exactly what I was needing.

Assign the macros to buttons, and perhaps write the state to another cell, along the lines of:

Worksheets("Other Sheet").Range("StatusCell").Value = "Values FROZEN"
or
Worksheets("Other Sheet").Range("StatusCell").Value = "Values Active"

......
but running them it's not user-friendly and that's because I think I'm
confused with these 2 above codes. Where do I put them? As a code in the
above macros? Macros are not a key strength of mine. I do get that what you
mean is having a button in the worksheet associated to an underlying macro,
and by clicking said button macro is activated. But how???

HTH,
Bernie
MS Excel MVP

....
I wrote you that for the time being I'm not having the inputs as dynamic
ranges. I decided there's always going to be 10 series of options in
DataLinks A2:A11. But I do want to have a routine so that the user at the
beginning of the exercize period types the options labels (e.g. PETRJ52,
PETRJ54,...) sequentially in this range, remaining there until new exercize
period starts. However, Input Box procedure allows me only having 1 input at
a time and the corresponding cell must be highlighted manually. Is there a
way to have dialog box inputting all 10 entries in a row?

Thank you very much for your time and attention. Most kind of you.

--
Dan GSB
Asset Management - Rio