The following code locks data onto column IV. So whenever a column elsewhere
is deleted the data will not shift to IU.



Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$IV$1:$IV$800"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
If CStr(ActiveWorkbook.Names(CStr(fixed_name)).Refers To) <
CStr(refer_str) Then
On Error GoTo CleanUp
Application.EnableEvents = False
Set rng = Evaluate(fixed_name)
save_formula = rng.Formula
rng.Value = ""
Set rng = Range(CStr(refer_str))
rng.Formula = save_formula
ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
Else
'do nothing
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub


What if I want to add a column somewhere on the sheet?

I could first delete an unused column somewhere on the sheet, but because
the code behind the sheet is to prevent the shift of data to field IU I am
therefore unable to add a new column/field.

How can I retain the code but also allow the addition of a new column/field?

Thanks if you are able to help.
Pat

Pat wrote:
What if I want to add a column somewhere on the sheet?

I could first delete an unused column somewhere on the sheet, but
because the code behind the sheet is to prevent the shift of data to
field IU I am therefore unable to add a new column/field.

How can I retain the code but also allow the addition of a new
column/field?

Thanks if you are able to help.
Pat

Hi Pat
you should be able to add a new column. What kind of error did you get

Frank

Only solution I can think of is to put your formulas on a second sheet
(possibly hidden) and rebuild your formulas from there (unless the formula
is really a single formula that can be multicell entered (ctrl+Enter). In
that case, you could just put the formula in the code.

Also, the code would have to be modified to search for a unique identifier
in the formula rather than relying on a named range.

Any of that sound appealing?

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
The following code locks data onto column IV. So whenever a column
elsewhere
is deleted the data will not shift to IU.



Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$IV$1:$IV$800"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
If CStr(ActiveWorkbook.Names(CStr(fixed_name)).Refers To) <
CStr(refer_str) Then
On Error GoTo CleanUp
Application.EnableEvents = False
Set rng = Evaluate(fixed_name)
save_formula = rng.Formula
rng.Value = ""
Set rng = Range(CStr(refer_str))
rng.Formula = save_formula
ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
Else
'do nothing
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub


What if I want to add a column somewhere on the sheet?

I could first delete an unused column somewhere on the sheet, but because
the code behind the sheet is to prevent the shift of data to field IU I am
therefore unable to add a new column/field.

How can I retain the code but also allow the addition of a new
column/field?

Thanks if you are able to help.
Pat

New column would force data in IV off the page and screw up your name
redefinition would it not?


refersto: =Ref#
--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Pat wrote:
What if I want to add a column somewhere on the sheet?

I could first delete an unused column somewhere on the sheet, but
because the code behind the sheet is to prevent the shift of data to
field IU I am therefore unable to add a new column/field.

How can I retain the code but also allow the addition of a new
column/field?

Thanks if you are able to help.
Pat

Hi Pat
you should be able to add a new column. What kind of error did you get

Frank

Tom Ogilvy wrote:
New column would force data in IV off the page and screw up your name
redefinition would it not?


refersto: =Ref#

correct. My problem with this issue is I think in total it is creating
more and more overhead :-). i'm still not convinced that this is all
rquired. But it is a nice excercise to 'lock' cells :-)

Regards
Frank

Hello gentlemen

An interesting discussion. I think your suggestion Tom would be like
building a Rolls Royce of a sheet :-)
I am not so sure if it would be worth going to that trouble and maybe
screw up the original sheet in the process.

Thanks anyway for the suggestion.

regards
Pat

"Tom Ogilvy" wrote in message
...
Only solution I can think of is to put your formulas on a second sheet
(possibly hidden) and rebuild your formulas from there (unless the formula
is really a single formula that can be multicell entered (ctrl+Enter). In
that case, you could just put the formula in the code.

Also, the code would have to be modified to search for a unique identifier
in the formula rather than relying on a named range.

Any of that sound appealing?

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
The following code locks data onto column IV. So whenever a column
elsewhere
is deleted the data will not shift to IU.



Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$IV$1:$IV$800"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
If CStr(ActiveWorkbook.Names(CStr(fixed_name)).Refers To) <
CStr(refer_str) Then
On Error GoTo CleanUp
Application.EnableEvents = False
Set rng = Evaluate(fixed_name)
save_formula = rng.Formula
rng.Value = ""
Set rng = Range(CStr(refer_str))
rng.Formula = save_formula
ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
Else
'do nothing
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub


What if I want to add a column somewhere on the sheet?

I could first delete an unused column somewhere on the sheet, but
because
the code behind the sheet is to prevent the shift of data to field IU I
am
therefore unable to add a new column/field.

How can I retain the code but also allow the addition of a new
column/field?

Thanks if you are able to help.
Pat

Actually, you only need the cells of interest on this "hidden" sheet.

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Hello gentlemen

An interesting discussion. I think your suggestion Tom would be like
building a Rolls Royce of a sheet :-)
I am not so sure if it would be worth going to that trouble and maybe
screw up the original sheet in the process.

Thanks anyway for the suggestion.

regards
Pat

"Tom Ogilvy" wrote in message
...
Only solution I can think of is to put your formulas on a second sheet
(possibly hidden) and rebuild your formulas from there (unless the
formula
is really a single formula that can be multicell entered (ctrl+Enter).
In
that case, you could just put the formula in the code.

Also, the code would have to be modified to search for a unique
identifier
in the formula rather than relying on a named range.

Any of that sound appealing?

--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
The following code locks data onto column IV. So whenever a column
elsewhere
is deleted the data will not shift to IU.



Private Sub Worksheet_Change(ByVal Target As Range)
Const fixed_name = "lock_formula"
Const refer_str = "=Sheet1!$IV$1:$IV$800"
Dim rng As Range
Dim save_formula

If IsError(Evaluate(fixed_name)) Then
ActiveWorkbook.Names.Add Name:=CStr(fixed_name),
RefersTo:=CStr(refer_str)
Else
If CStr(ActiveWorkbook.Names(CStr(fixed_name)).Refers To) <
CStr(refer_str) Then
On Error GoTo CleanUp
Application.EnableEvents = False
Set rng = Evaluate(fixed_name)
save_formula = rng.Formula
rng.Value = ""
Set rng = Range(CStr(refer_str))
rng.Formula = save_formula
ActiveWorkbook.Names(CStr(fixed_name)).RefersTo =
CStr(refer_str)
Else
'do nothing
End If
End If

CleanUp:
Application.EnableEvents = True
End Sub


What if I want to add a column somewhere on the sheet?

I could first delete an unused column somewhere on the sheet, but
because
the code behind the sheet is to prevent the shift of data to field IU
I
am
therefore unable to add a new column/field.

How can I retain the code but also allow the addition of a new
column/field?

Thanks if you are able to help.
Pat