Adding a new column/field

  • Thread starter Thread starter Pat
  • Start date Start date
P

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
 
Pat said:
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
Click to expand...

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?
 
New column would force data in IV off the page and screw up your name
redefinition would it not?


refersto: =Ref#
 
Tom said:
New column would force data in IV off the page and screw up your name
redefinition would it not?


refersto: =Ref#
Click to expand...

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
 
Back
Top