Update cells with force input (2000 sr 1)

[sal21]

Thos wbook contain 2 sheet with the same value into column E.
I would want to uppdte the cells into sheet OUT column E, from column E of sheet IN...

but with this controll:

if i insert a value with 3 digit in E5 into sheet IN show msg box "Attention max 2 Character!" or if the value insert in E5 is diffrent from the table into into range P3:P10 show msgbox "attention not valid value!"

[HansV]

1) Do you want to update column E in the OUT sheet with existing values in the IN sheet, or do you want to update when the user enters a new value in the IN sheet?

2) You can use Data | Validation to allow the user to enter only values from the list in P3:P10 into column E.

[sal21]

update the related value into sheet OUT from related value into sheet IN...
Example: If i insert a new value into E12 in sheet IN, update E12 into sheet OUT with the value inserted into E12 of sheet In...

[sdckapr]

This might be trivial, but how about with the formula in sheet Out in E12:
<pre>=IN!E12</pre>


Though perhaps I misunderstand...

Steve

[HansV]

Steve's suggestion is by far the easiest method. You can also use the worksheet change event:
- Right-click the sheet tab of the IN sheet.
- Select View Code from the popup menu.
- Copy the following code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("E3:E65536")) Is Nothing Then
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("E3:E65536")).Cells
Worksheets("OUT").Range(oCell.Address).Value = oCell.Value
Next oCell
Application.EnableEvents = True
End If
End Sub

[sal21]

GOOD! Tks.

[sal21]

good morning Hans...
And for the famous 2 msgbox... you have suggest me:

2) You can use Data | Validation to allow the user to enter only values from the list in P3:P10 into column E.

but into this sheet i already have this macro into evevnt "Worksheet_SelectionChange", and when i make a new validation set Excel tell me: not possible to insert 2 validation into same sheet....:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws1 As Worksheet, ws2 As Worksheet
Dim val1 As Variant
Dim val2 As String
Dim rg1 As Range, rg2 As Range

Set rg1 = Range("E3:E1000") 'Only display "Tooltip" for these cells
If Intersect(Target, rg1) Is Nothing Then Exit Sub

Set ws2 = Sheets("SALDI") ' this is the sheet where the correspronding values are
Set rg2 = ws2.Range("P3:Q10") 'this is the range to look it up
val1 = Target.Cells(1, 1).Value
If val1 <> "" Then
val2 = WorksheetFunction.VLookup(val1, rg2, 2)
With Target.Cells(1, 1).Validation
.Delete
.Add Type:=xlValidateInputOnly
.InputTitle = "NOTE: "
.InputMessage = val2
End With
End If
End Sub

[HansV]

Why are you setting validation in code?

[sal21]

But you have suggest me:
2) You can use Data | Validation to allow the user to enter only values from the list in P3:P10 into column E.
... peraphs your suggestion not is refered for the 2 MsgBox?

[HansV]

I suggested that you use the Validation option in the Data menu, not that you set validation in code.

[sal21]

i am sorry....
But to insert this 2 condition in data entry in column E when i isnert a value?

this is the 2 controll:

if i insert a value with 3 digit, for example, in one cell of range column E into sheet IN show msg box "Attention max 2 Character!" or if the value insert in one cell of range column E a diffrent value from the table into into range P3:P10 show msgbox "attention not valid value!"

[HansV]

See the attached (zipped) version. I have combined the validation and copy code into the Worksheet_Change event.

[sal21]

Perfect!
Tks as usual...
Sal.