title: Excel VBA table entry validation
body: <p>I have a table created in excel (named WaterTable) and have a userform which allows data to be entered into it.  The columns are: YEAR, QUARTER, SOURCE, VOLUME.</p>
  
  <p>Looking around on here and other locations I have managed to get sorted with code that allows users to enter data into the boxes on the form and submit - with the code adding a new row and entering their data.</p>
  
  <p>However, where I am struggling is that at the moment a user could put in:</p>
  
  <pre><code>YEAR - QUARTER - SOURCE - VOLUME
  2013 -    4    -  MAINS -   50
  </code></pre>
  
  <p>And that's no problem - BUT if they enter a wrong number and want to re-enter it will just create a new line, but retain the original entry.  This causes problems for presenting the data (multiple entries) and also means errors cannot be corrected.  I have managed to look around and put the following code in:</p>
  
  <pre><code>    Set sheet = ThisWorkbook.Worksheets("Raw Data")
      Set table = sheet.ListObjects.Item("WaterTable")
      Set EvalRange = table.DataBodyRange
  
  If WorksheetFunction.CountIf(EvalRange, YearBox.Text) &gt; 1 And WorksheetFunction.CountIf(EvalRange, QuarterBox.Text) &gt; 1 And WorksheetFunction.CountIf(EvalRange, SourceBox.Text) &gt; 1 Then
      MsgBox "Data Entry Already Present"
      Application.EnableEvents = False
      Else
      Call AddDataRow
  End If
  </code></pre>
  
  <p>Which partially solves my problem - as now you can only enter one 'volume' data point for a particular year, quarter and source.  Which works to an extent - but what I really want is for the message box to give option to either replace the current data OR close.  So that wrongly entered data can be corrected.  Unfortunately I've come across a brick wall on that one and can't think what to use!</p>
  
  <p>Hope that makes sense!</p>
  
  <p>AS ADDITION FOLLOWING COMMENT MADE BELOW:</p>
  
  <p>I've changed to using 'find' command, and come up with the following code to start me off:</p>
  
  <pre><code>SearchString = YearBox.Value
  On Error Resume Next
  Set bCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
              MatchCase:=False, SearchFormat:=False).Offset(0, 3)
  If Not bCell Is Nothing Then
         MsgBox "Checking data Validity"
      ReplaceData = bCell.Address
  End If
  
  Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
              MatchCase:=False, SearchFormat:=False)
  
  If Not aCell Is Nothing Then
      FoundAt = aCell.Address
     Select Case MsgBox("The entered exists: " &amp; FoundAt &amp; "   " &amp; ReplaceData &amp; "  Replace?", vbYesNo)
      Case vbNo
      MsgBox "data entry cancelled"
      Case vbYes
      Select Case MsgBox("This will replace data: Are You Sure?", vbYesNo)
          Case vbYes
          bCell = VolBox.Value
          Case vbNo
          Call CommandButton1_Click
      End Select
  End Select
  Else
      MsgBox "Unique Data Entry Confirmed - submit data?", vbOKCancel
      Call AddDataRow
  End If
  </code></pre>
  
  <p>Not terribly elegant unfortunately (and I don't know why the first 'find' for bCell fails if the entry is not found - although the aCell which seems to be virtually the same works fine?? Hence the resume on error).</p>
  
  <p>This however will only check one data point (in this case year) so I can find out if the year column matches and then make a change to the volume data if the user wants to or stop if they don't.  What I can't do is use multiple criteria.  I don't see a way to have 'AND' with this?</p>