Consulting

Results 1 to 7 of 7

Thread: Check if Value Exists in Another Sheet

  1. #1
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location

    Exclamation Check if Value Exists in Another Sheet

    Hello, how do I change the below code so when I enter a value on column M via paste it will return a messagebox saying "invalid data"?
    the below code works however it isn't limited to just column M. it happens to all cells

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim FindString As String
    Dim Rng As Range
    On Error Resume Next
    
    
    FindString = Range("M2").Value
    
    
    If Trim(FindString) <> "" Then
        With Sheets("IDs").Range("A:A") 'searches all of column A
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                
            Else
                MsgBox "Invalid Data!" 'value not found
                Target.Clear
                
            End If
        End With
    End If
        
    End Sub
    Please help thank you
    Last edited by jazz2409; 06-01-2020 at 08:23 AM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not tested



    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFind as Range
    Dim FindString As String
    Dim Rng As Range
    On Error Resume Next
    
    Set rFind = Target.Cells(1,1)
    
    If rFind.Column <> 13 Then Exit Sub
    
    FindString = rFind.Value
    
    
    If Trim(FindString) <> "" Then
        With Sheets("IDs").Range("A:A") 'searches all of column A
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                
            Else
                MsgBox "Invalid Data!" 'value not found
                rFind.Clear
                
            End If
        End With
    End If
         End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Try this way. Use ClearContents, Clear will remove everything, including formatting. I don't think you need the variable either.

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.CountLarge > 1 Or Target.Column <> 13 Or IsEmpty(Target) Then Exit Sub
        On Error Resume Next
        If Application.WorksheetFunction.CountIf(Sheets("IDs").Range("A:A"), Trim(Target.Value)) > 0 Then
            MsgBox "Invalid Data!"    'value exists
            Target.ClearContents
            Exit Sub
        End If
        On Error GoTo 0
    End Sub
    Hope that helps.
    Get the free Excel based Toolbar or click here for free examples & Excel Consultancy

    RoyUK

  4. #4
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Both worked great!
    Just to share, this is what I came up with on my own:

    Private Sub Workbook_Open()
    
    Dim rng As Range
    
    
    lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'gets number of last used cell
    Set rng = Range("M2:M" & lastrow)
    
    
    With rng.Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:= _
            "=IF(ISERROR(VLOOKUP(M2,IDs!$A$2:$A$65536,1,FALSE)),FALSE,TRUE)"
            .ErrorMessage = "INVALID DATA!"
        End With
    End Sub

    May not be the best out there but works just the same
    I used workbook_open() because my friend's file is in xls

  5. #5
    You've moved the code to a different event.

    If you are going to use Data Validation then why use VBA?
    Hope that helps.
    Get the free Excel based Toolbar or click here for free examples & Excel Consultancy

    RoyUK

  6. #6
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by royUK View Post
    You've moved the code to a different event.

    If you are going to use Data Validation then why use VBA?
    The file is in xls and won't accept data validation.. Also my friend's client only wants data validation

  7. #7
    xls files can have data validation, it wouldn't work with VBA if that was not the case.
    Hope that helps.
    Get the free Excel based Toolbar or click here for free examples & Excel Consultancy

    RoyUK

Tags for this Thread

Posting Permissions

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