No announcement yet.

message box for keyed or pasted data

  • Filter
  • Time
  • Show
Clear All
new posts

  • message box for keyed or pasted data

    I have a spreadsheet (attached) that the customer is to fill out for transfers. Sheet 1 contains a list of part numbers in column A, this is hidden from the customer's view. Sheet 2 has the data they enter. If they enter anything into column B (Requested Material column) that exists on the list in Sheet 1, I need a message to pop up stating that they can't transfer that material, then forcing them to re-key the entry.
    Here's the issue, if they cut the data from the SAP system and paste it into the spreadsheet, I need the message box to still appear. The only examples that I've seen thus far work if they hand-key the part number in, but not if they paste the part number in. Surely this is possible? I've attached a sample of the spreadsheet.
    Thank you!
    Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

  • #2
    It is better to attach the file rather than link it.

    Right click Sheet2, View Code, and paste.
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim r As Range, c As Range, s As Range
      Set r = Intersect(Target, Range("B3", Cells(Rows.Count, "B").End(xlUp)))
      If r Is Nothing Then Exit Sub
      Set s = Worksheets(1).Range("A2", Worksheets(1).Cells(Rows.Count, "A").End(xlUp))
      'On Error Resume Next
      Application.EnableEvents = False
      For Each c In r
        If WorksheetFunction.CountIf(s, c.Value) = 0 Then c.Value = "NA"
      Next c
      Application.EnableEvents = True
    End Sub
    As you can see, if not value is matched, NA is used. At workbook Close event, you can iterate the NA cells to take further action.


    • #3
      That works with one exception, unfortunately if I cut/paste the number into the spreadsheet, it doesn't show the message box and require re-entry.


      • #4

        All you have to do is to write a routine to check for NA's and then do what you want. You may also want to check for empty or blank or null values. You then add a call to that routine after the For loop. This makes it modular and easy to test and maintain. Up to you though how that is done.

        If NA, rather than MsgBox, I would use a modeless Userform. That lets the user make changes with dialog visible. When closed, if still NAs, macro would repeat. If they still have NA's, in the Workbook's Close and/or save event, you can then call your check for NA values routine again just to be sure. So, modular is handy.

        Which do you want, a MsgBox or a Userform? Another thought is that the macro would autofilter the column by NA values. That makes it easy to see and handle all NA values.