Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update cells with force input (2000 sr 1)

    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!"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update cells with force input (2000 sr 1)

    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.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cells with force input (2000 sr 1)

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Update cells with force input (2000 sr 1)

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


    Though perhaps I misunderstand...

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update cells with force input (2000 sr 1)

    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

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cells with force input (2000 sr 1)

    GOOD! Tks.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cells with force input (2000 sr 1)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update cells with force input (2000 sr 1)

    Why are you setting validation in code?

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cells with force input (2000 sr 1)

    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?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update cells with force input (2000 sr 1)

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

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cells with force input (2000 sr 1)

    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!"

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update cells with force input (2000 sr 1)

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

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update cells with force input (2000 sr 1)

    Perfect!
    Tks as usual...
    Sal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •