Adding a new column/field

[Pat]

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)).RefersTo) <>
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

 

[Frank Kabel]

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]

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?

 

[Tom Ogilvy]

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


refersto: =Ref#

 

[Frank Kabel]

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

 

[Pat]

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]

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