Locking data

  • Thread starter Thread starter Antony
  • Start date Start date
A

Antony

Hi,

Is there any way that once a user has clicked a button or entered data into
a cell it is locked in an equivalent cell? For example

in Cell B1 formula =A1
in Cell A1 = 5

If the 5 becomes 6 then so will B1. But is there anyway, that by using a
formula or short onClick code I can make it so that B1 remains 5 even if A1
changes.

Thanks

Antony
 
Right click on the worksheet tab that should exhibit this behavior and select
view code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
With Target
If IsEmpty(.Offset(0, 1)) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Value
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


When you type something into A1, it checks to see if B1 (.offset(0,1) is one
cell to the right) is empty. If it's empty, then it fills in what was in A1.
If it's not empty, it just quits.
 
I'm not sure I understand what one set means, but maybe just changing this line
to include more stuff:

from:
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub
to:
If Intersect(Target, Range("a1,b3,c5")) Is Nothing Then Exit Sub
or even this to get a couple of columns:
If Intersect(Target, Range("a:a,c:c")) Is Nothing Then Exit Sub

would do what you want
or
 
That is definitely what I mean. I have absolutely no clue (as you can
probably guess) about VB. I can do a bit of Javascript tinkering but that's
about the extent of my knowledge. Is there anyway, that I can do this:

Cell A1 is the source
Cell B1 is (as already given the locked code)

But in A1 there is a formula IF(A5>0,A5,"") so that B1 will only be
locked if a source figure is greater than zero - i.e. A1 is a formula but
which will only lock the data if there is something >0 to actually lock.

I appreciate your help.

Antony
 
I think that this works:

Option Explicit
Private Sub Worksheet_Calculate()

On Error GoTo errHandler:

If Not IsEmpty(Me.Range("b1")) Then Exit Sub

If Me.Range("a1").Value <> "" Then
Application.EnableEvents = False
Me.Range("b1").Value = Me.Range("a1").Value
End If

errHandler:
Application.EnableEvents = True

End Sub

Notice that we shifted from worksheet_change to worksheet_calculate. Excel
needs to do a calculation to have the fire. But since you have a formula in A1
that depends on A5, it'll fire when A5 changes.

And if you have more than one cell:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRng As Range
Dim myCell As Range

'these are the "frozen cells"
Set myRng = Me.Range("b1,c8,D10")

On Error GoTo errHandler:
Application.EnableEvents = False

For Each myCell In myRng.Cells
If IsEmpty(myCell) Then
If myCell.Column > 1 Then
If myCell.Offset(0, -1).Value <> "" Then
myCell.Value = myCell.Offset(0, -1).Value
End If
End If
End If
Next myCell

errHandler:
Application.EnableEvents = True

End Sub




That is definitely what I mean. I have absolutely no clue (as you can
probably guess) about VB. I can do a bit of Javascript tinkering but that's
about the extent of my knowledge. Is there anyway, that I can do this:

Cell A1 is the source
Cell B1 is (as already given the locked code)

But in A1 there is a formula IF(A5>0,A5,"") so that B1 will only be
locked if a source figure is greater than zero - i.e. A1 is a formula but
which will only lock the data if there is something >0 to actually lock.

I appreciate your help.

Antony
Click to expand...
 
Back
Top