Presearch column when entering entering data with a user form to prevent duplicates












0















looking to adjust my VBA code noted below for a user form i'm using to enter in rows of data. I want to have the user form present a dialog box noting that the claim number in this instance exists in column B when filling out the user form. Ideally i would like this to occur prior to the row data being populated, any help provided will be very much appreacited.



Private Sub AddTable_Click()
Sheets("2018 (1st Party)").Unprotect
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("2018 (1st Party)")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

'check for a Claim Number'
If Trim(Me.ClaimEntry.Value) = "" Then
Me.ClaimEntry.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'check for a State Value'
If Trim(Me.StateEntry.Value) = "" Then
Me.ClaimEntry.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'Find Revision'
If RevisionYes.Value = True Then
ws.Cells(iRow, 12).Value = "Yes"
ElseIf RevisionNo = True Then
ws.Cells(iRow, 12).Value = "No"
Else
End If

'Find Returned'
If ReturnedYes.Value = True Then
ws.Cells(iRow, 13).Value = "Yes"
ElseIf ReturnedNo = True Then
ws.Cells(iRow, 13).Value = "No"
Else
End If

'Find Party'
If PartyYes.Value = True Then
ws.Cells(iRow, 14).Value = "1st"
ElseIf PartyNo = True Then
ws.Cells(iRow, 14).Value = "3rd"
Else
End If



'copy the data to the database
ws.Cells(iRow, 1).Value = Me.DateEntry.Value
ws.Cells(iRow, 2).Value = Me.ClaimEntry.Value
ws.Cells(iRow, 3).Value = Me.StateEntry.Value
ws.Cells(iRow, 4).Value = Me.INSD_CLMTentry.Value
ws.Cells(iRow, 5).Value = Me.IAFirmEntry.Value
ws.Cells(iRow, 6).Value = Me.IA_Last_NameEntry.Value
ws.Cells(iRow, 7).Value = Me.EstEntry.Value
ws.Cells(iRow, 8).Value = Me.RevisedEntry.Value
ws.Cells(iRow, 15).Value = Me.CommentsEntry.Value

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data

Me.ClaimEntry.Value = ""
Me.StateEntry.Value = ""
Me.INSD_CLMTentry.Value = ""
Me.IAFirmEntry.Value = ""
Me.IA_Last_NameEntry = ""
Me.EstEntry = ""
Me.RevisedEntry.Value = ""
Me.CommentsEntry.Value = ""
Me.DateEntry.Value = ""

Me.ClaimEntry.SetFocus


Sheets("2018 (1st Party)").Protect
End Sub









share|improve this question





























    0















    looking to adjust my VBA code noted below for a user form i'm using to enter in rows of data. I want to have the user form present a dialog box noting that the claim number in this instance exists in column B when filling out the user form. Ideally i would like this to occur prior to the row data being populated, any help provided will be very much appreacited.



    Private Sub AddTable_Click()
    Sheets("2018 (1st Party)").Unprotect
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("2018 (1st Party)")

    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

    'check for a Claim Number'
    If Trim(Me.ClaimEntry.Value) = "" Then
    Me.ClaimEntry.SetFocus
    MsgBox "Please complete the form"
    Exit Sub
    End If

    'check for a State Value'
    If Trim(Me.StateEntry.Value) = "" Then
    Me.ClaimEntry.SetFocus
    MsgBox "Please complete the form"
    Exit Sub
    End If

    'Find Revision'
    If RevisionYes.Value = True Then
    ws.Cells(iRow, 12).Value = "Yes"
    ElseIf RevisionNo = True Then
    ws.Cells(iRow, 12).Value = "No"
    Else
    End If

    'Find Returned'
    If ReturnedYes.Value = True Then
    ws.Cells(iRow, 13).Value = "Yes"
    ElseIf ReturnedNo = True Then
    ws.Cells(iRow, 13).Value = "No"
    Else
    End If

    'Find Party'
    If PartyYes.Value = True Then
    ws.Cells(iRow, 14).Value = "1st"
    ElseIf PartyNo = True Then
    ws.Cells(iRow, 14).Value = "3rd"
    Else
    End If



    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.DateEntry.Value
    ws.Cells(iRow, 2).Value = Me.ClaimEntry.Value
    ws.Cells(iRow, 3).Value = Me.StateEntry.Value
    ws.Cells(iRow, 4).Value = Me.INSD_CLMTentry.Value
    ws.Cells(iRow, 5).Value = Me.IAFirmEntry.Value
    ws.Cells(iRow, 6).Value = Me.IA_Last_NameEntry.Value
    ws.Cells(iRow, 7).Value = Me.EstEntry.Value
    ws.Cells(iRow, 8).Value = Me.RevisedEntry.Value
    ws.Cells(iRow, 15).Value = Me.CommentsEntry.Value

    MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
    'clear the data

    Me.ClaimEntry.Value = ""
    Me.StateEntry.Value = ""
    Me.INSD_CLMTentry.Value = ""
    Me.IAFirmEntry.Value = ""
    Me.IA_Last_NameEntry = ""
    Me.EstEntry = ""
    Me.RevisedEntry.Value = ""
    Me.CommentsEntry.Value = ""
    Me.DateEntry.Value = ""

    Me.ClaimEntry.SetFocus


    Sheets("2018 (1st Party)").Protect
    End Sub









    share|improve this question



























      0












      0








      0








      looking to adjust my VBA code noted below for a user form i'm using to enter in rows of data. I want to have the user form present a dialog box noting that the claim number in this instance exists in column B when filling out the user form. Ideally i would like this to occur prior to the row data being populated, any help provided will be very much appreacited.



      Private Sub AddTable_Click()
      Sheets("2018 (1st Party)").Unprotect
      Dim iRow As Long
      Dim ws As Worksheet
      Set ws = Worksheets("2018 (1st Party)")

      'find first empty row in database
      iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

      'check for a Claim Number'
      If Trim(Me.ClaimEntry.Value) = "" Then
      Me.ClaimEntry.SetFocus
      MsgBox "Please complete the form"
      Exit Sub
      End If

      'check for a State Value'
      If Trim(Me.StateEntry.Value) = "" Then
      Me.ClaimEntry.SetFocus
      MsgBox "Please complete the form"
      Exit Sub
      End If

      'Find Revision'
      If RevisionYes.Value = True Then
      ws.Cells(iRow, 12).Value = "Yes"
      ElseIf RevisionNo = True Then
      ws.Cells(iRow, 12).Value = "No"
      Else
      End If

      'Find Returned'
      If ReturnedYes.Value = True Then
      ws.Cells(iRow, 13).Value = "Yes"
      ElseIf ReturnedNo = True Then
      ws.Cells(iRow, 13).Value = "No"
      Else
      End If

      'Find Party'
      If PartyYes.Value = True Then
      ws.Cells(iRow, 14).Value = "1st"
      ElseIf PartyNo = True Then
      ws.Cells(iRow, 14).Value = "3rd"
      Else
      End If



      'copy the data to the database
      ws.Cells(iRow, 1).Value = Me.DateEntry.Value
      ws.Cells(iRow, 2).Value = Me.ClaimEntry.Value
      ws.Cells(iRow, 3).Value = Me.StateEntry.Value
      ws.Cells(iRow, 4).Value = Me.INSD_CLMTentry.Value
      ws.Cells(iRow, 5).Value = Me.IAFirmEntry.Value
      ws.Cells(iRow, 6).Value = Me.IA_Last_NameEntry.Value
      ws.Cells(iRow, 7).Value = Me.EstEntry.Value
      ws.Cells(iRow, 8).Value = Me.RevisedEntry.Value
      ws.Cells(iRow, 15).Value = Me.CommentsEntry.Value

      MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
      'clear the data

      Me.ClaimEntry.Value = ""
      Me.StateEntry.Value = ""
      Me.INSD_CLMTentry.Value = ""
      Me.IAFirmEntry.Value = ""
      Me.IA_Last_NameEntry = ""
      Me.EstEntry = ""
      Me.RevisedEntry.Value = ""
      Me.CommentsEntry.Value = ""
      Me.DateEntry.Value = ""

      Me.ClaimEntry.SetFocus


      Sheets("2018 (1st Party)").Protect
      End Sub









      share|improve this question
















      looking to adjust my VBA code noted below for a user form i'm using to enter in rows of data. I want to have the user form present a dialog box noting that the claim number in this instance exists in column B when filling out the user form. Ideally i would like this to occur prior to the row data being populated, any help provided will be very much appreacited.



      Private Sub AddTable_Click()
      Sheets("2018 (1st Party)").Unprotect
      Dim iRow As Long
      Dim ws As Worksheet
      Set ws = Worksheets("2018 (1st Party)")

      'find first empty row in database
      iRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row

      'check for a Claim Number'
      If Trim(Me.ClaimEntry.Value) = "" Then
      Me.ClaimEntry.SetFocus
      MsgBox "Please complete the form"
      Exit Sub
      End If

      'check for a State Value'
      If Trim(Me.StateEntry.Value) = "" Then
      Me.ClaimEntry.SetFocus
      MsgBox "Please complete the form"
      Exit Sub
      End If

      'Find Revision'
      If RevisionYes.Value = True Then
      ws.Cells(iRow, 12).Value = "Yes"
      ElseIf RevisionNo = True Then
      ws.Cells(iRow, 12).Value = "No"
      Else
      End If

      'Find Returned'
      If ReturnedYes.Value = True Then
      ws.Cells(iRow, 13).Value = "Yes"
      ElseIf ReturnedNo = True Then
      ws.Cells(iRow, 13).Value = "No"
      Else
      End If

      'Find Party'
      If PartyYes.Value = True Then
      ws.Cells(iRow, 14).Value = "1st"
      ElseIf PartyNo = True Then
      ws.Cells(iRow, 14).Value = "3rd"
      Else
      End If



      'copy the data to the database
      ws.Cells(iRow, 1).Value = Me.DateEntry.Value
      ws.Cells(iRow, 2).Value = Me.ClaimEntry.Value
      ws.Cells(iRow, 3).Value = Me.StateEntry.Value
      ws.Cells(iRow, 4).Value = Me.INSD_CLMTentry.Value
      ws.Cells(iRow, 5).Value = Me.IAFirmEntry.Value
      ws.Cells(iRow, 6).Value = Me.IA_Last_NameEntry.Value
      ws.Cells(iRow, 7).Value = Me.EstEntry.Value
      ws.Cells(iRow, 8).Value = Me.RevisedEntry.Value
      ws.Cells(iRow, 15).Value = Me.CommentsEntry.Value

      MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
      'clear the data

      Me.ClaimEntry.Value = ""
      Me.StateEntry.Value = ""
      Me.INSD_CLMTentry.Value = ""
      Me.IAFirmEntry.Value = ""
      Me.IA_Last_NameEntry = ""
      Me.EstEntry = ""
      Me.RevisedEntry.Value = ""
      Me.CommentsEntry.Value = ""
      Me.DateEntry.Value = ""

      Me.ClaimEntry.SetFocus


      Sheets("2018 (1st Party)").Protect
      End Sub






      excel vba






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 12 '18 at 17:11









      p._phidot_

      7001315




      7001315










      asked Nov 12 '18 at 14:59









      Samuel LorettaSamuel Loretta

      11




      11
























          0






          active

          oldest

          votes











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264804%2fpresearch-column-when-entering-entering-data-with-a-user-form-to-prevent-duplica%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264804%2fpresearch-column-when-entering-entering-data-with-a-user-form-to-prevent-duplica%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Full-time equivalent

          さくらももこ

          13 indicted, 8 arrested in Calif. drug cartel investigation