+ Reply to Thread
Results 1 to 8 of 8

Need If else then statement for when the value does not match the txtbox on the edit tab

  1. #1
    Registered User
    Join Date
    10-31-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Post Need If else then statement for when the value does not match the txtbox on the edit tab

    Private Sub CommandButton1_Click()


    Dim TargetRow As Integer 'variable to save position of this person in database

    'use Match worksheet function to find position of chosen name

    'If ColumnE_Menu = Application.WorksheetFunction.Match(ColumnE_Menu, Sheets("Data").Range("Dyn_Full_Name")) Then

    TargetRow = Application.WorksheetFunction.Match(ColumnE_Menu, Sheets("Data").Range("Dyn_Full_Name"), 0)
    Sheets("Engine").Range("B5").Value = TargetRow 'save position in the engine, for use later

    'Else: MsgBox "No FIle Number Found"


    'End If

    Unload Find_Entry_UF 'unload the userform to select a name

    '''Begin retrieving data from database'''
    Data_UF.Txt_OurFile = Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value 'our file number
    Data_UF.Txt_ThereFile = Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value 'there file number
    Data_UF.Txt_ClaimNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value 'claim number
    Data_UF.Txt_Policy = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value 'policy number
    Data_UF.Txt_Date = Sheets("Data").Range("Data_Start").Offset(TargetRow, 5).Value 'date
    Data_UF.Txt_InsCo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value 'Insurance Company
    Data_UF.Txt_Vehicle = Sheets("Data").Range("Data_Start").Offset(TargetRow, 7).Value 'Vehicle
    Data_UF.Txt_OurAppraiser = Sheets("Data").Range("Data_Start").Offset(TargetRow, 8).Value 'Our Appraiser

    'begin conditional statement to control assignment complete
    If Sheets("Data").Range("Data_Start").Offset(TargetRow, 9).Value = "Yes" Then
    Data_UF.Option_Y_Child = True
    Else
    Data_UF.Option_N_Child = True
    End If 'end conditional statement to control assignment complete status

    Data_UF.Combo_ReceivedPay = Sheets("Data").Range("Data_Start").Offset(TargetRow, 10).Value 'received pay
    Data_UF.Combo_AppPaid = Sheets("Data").Range("Data_Start").Offset(TargetRow, 11).Value 'appraiser paid

    '''End retrieving data from database'''

    Data_UF.Caption = "Edit Existing" 'set caption to show that the user is editing
    Data_UF.Show 'show the user form with the details loaded in

    End Sub
    Attached Files Attached Files
    Last edited by myerays; 10-31-2020 at 04:38 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,239

    Re: Need If else then statement for when the value does not match the txtbox on the edit t

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    10-31-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Smile Re: Need If else then statement for when the value does not match the txtbox on the edit t

    Thank you so much. One other thing is I want it to close down the form without it opening the other form is no match is found.
    Thanks in advance


    Private Sub CommandButton1_Click()



    Dim TargetRow As Variant 'variable to save position of this person in database

    'use Match worksheet function to find position of chosen name
    TargetRow = Application.Match(ColumnE_Menu, Sheets("Data").Range("Dyn_Full_Name"), 0)

    If IsNumeric(TargetRow) Then
    Sheets("Engine").Range("B5").Value = TargetRow 'save position in the engine, for use later
    Else: MsgBox "No FIle Number Found"
    'I want it to close the forms and then say "No File Number Found" And not Run The Unload Find_Entry_UF
    End If

    'Else: MsgBox "No FIle Number Found"


    'End If

    Unload Find_Entry_UF 'unload the userform to select a name

    '''Begin retrieving data from database'''
    Data_UF.Txt_OurFile = Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value 'our file number
    Data_UF.Txt_ThereFile = Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value 'there file number
    Data_UF.Txt_ClaimNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value 'claim number
    Data_UF.Txt_Policy = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value 'policy number
    Data_UF.Txt_Date = Sheets("Data").Range("Data_Start").Offset(TargetRow, 5).Value 'date
    Data_UF.Txt_InsCo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value 'Insurance Company
    Data_UF.Txt_Vehicle = Sheets("Data").Range("Data_Start").Offset(TargetRow, 7).Value 'Vehicle
    Data_UF.Txt_OurAppraiser = Sheets("Data").Range("Data_Start").Offset(TargetRow, 8).Value 'Our Appraiser

    'begin conditional statement to control assignment complete
    If Sheets("Data").Range("Data_Start").Offset(TargetRow, 9).Value = "Yes" Then
    Data_UF.Option_Y_Child = True
    Else
    Data_UF.Option_N_Child = True
    End If 'end conditional statement to control assignment complete status

    Data_UF.Combo_ReceivedPay = Sheets("Data").Range("Data_Start").Offset(TargetRow, 10).Value 'received pay
    Data_UF.Combo_AppPaid = Sheets("Data").Range("Data_Start").Offset(TargetRow, 11).Value 'appraiser paid

    '''End retrieving data from database'''

    Data_UF.Caption = "Edit Existing" 'set caption to show that the user is editing
    Data_UF.Show 'show the user form with the details loaded in

    End Sub
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-31-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Smile Re: Need If else then statement for when the value does not match the txtbox on the edit t

    Thank you for your help.
    I would like for it to close the userform and not run the the rest of the code if no match of the file number.

    Thank you

    Private Sub CommandButton1_Click()



    Please Login or Register  to view this content.
    '''Begin retrieving data from database'''
    Data_UF.Txt_OurFile = Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value 'our file number
    Data_UF.Txt_ThereFile = Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value 'there file number
    Data_UF.Txt_ClaimNo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value 'claim number
    Data_UF.Txt_Policy = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value 'policy number
    Data_UF.Txt_Date = Sheets("Data").Range("Data_Start").Offset(TargetRow, 5).Value 'date
    Data_UF.Txt_InsCo = Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value 'Insurance Company
    Data_UF.Txt_Vehicle = Sheets("Data").Range("Data_Start").Offset(TargetRow, 7).Value 'Vehicle
    Data_UF.Txt_OurAppraiser = Sheets("Data").Range("Data_Start").Offset(TargetRow, 8).Value 'Our Appraiser

    'begin conditional statement to control assignment complete
    If Sheets("Data").Range("Data_Start").Offset(TargetRow, 9).Value = "Yes" Then
    Data_UF.Option_Y_Child = True
    Else
    Data_UF.Option_N_Child = True
    End If 'end conditional statement to control assignment complete status

    Data_UF.Combo_ReceivedPay = Sheets("Data").Range("Data_Start").Offset(TargetRow, 10).Value 'received pay
    Data_UF.Combo_AppPaid = Sheets("Data").Range("Data_Start").Offset(TargetRow, 11).Value 'appraiser paid

    '''End retrieving data from database'''

    Data_UF.Caption = "Edit Existing" 'set caption to show that the user is editing
    Data_UF.Show 'show the user form with the details loaded in

    End Sub
    Attached Files Attached Files
    Last edited by myerays; 11-01-2020 at 07:17 PM. Reason: Updated and highlighted

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,634

    Re: Need If else then statement for when the value does not match the txtbox on the edit t

    Please take a moment to read our forum rules...here
    Your post does not comply with Rule # 2
    2. Programming code must be enclosed in code tags to improve readability. (A, Z)
    Please Login or Register  to view this content.
    So...Edit your post...Highlight the code and press the # button
    We can only assist once this has been done…
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  6. #6
    Registered User
    Join Date
    10-31-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Need If else then statement for when the value does not match the txtbox on the edit t

    Thank you Very Much

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,239

    Re: Need If else then statement for when the value does not match the txtbox on the edit t

    You got the code tags around a little bit of your code. Close enough for now, but can you see you have three posts with almost all code. Maybe you want to go back and fix is all.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-31-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Red face Re: Need If else then statement for when the value does not match the txtbox on the edit t

    Here is a finished working model. This form uses the "our File Number" to search edit or add data to the data tab it also uses dynamic names.
    it keeps the UserForm Find_Entry_UF open which now has more options

    Feel free to use and change to your needs.

    Private Sub CommandButton1_Click()


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by myerays; 11-02-2020 at 01:32 PM. Reason: Updated and highlighted

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Check box if true copy txtbox 1 to txtbox 2
    By mmikem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2019, 02:27 PM
  2. [SOLVED] Can't edit ElseIf statement??
    By bralew in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-20-2017, 06:14 AM
  3. Move to next txtbox
    By gelandl in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-19-2008, 06:23 AM
  4. txtbox Data Validation
    By JIBG in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-28-2007, 01:43 PM
  5. txtbox focus
    By marcospaterson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2007, 03:10 PM
  6. txtbox problem
    By tammywilliams1604@yahoo.co.uk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2005, 01:06 PM
  7. Default value in Txtbox
    By Baxter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2005, 09:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1