Userform: If Statements are not working correctly

[JDR444]

Hello, I'm new here AND new to writing code, so I'm not even sure how to word my question, but I'm stuck and need help.

• I have a large database and userform.
• Data which includes (but not limited to)

1. Quickbooks file name = Txt_QB_File_Name
2. Full Name = FullName (as variable)
3. Business Name = Txt_Commercial_Name
​

• I can’t have duplicates on any of the above fields.
• Also, if the user enters the Txt_Commercial_Name, she won’t be adding a FullName.

I believe my problem lies whereas there WILL be empty spaces in the FullName if a Txt_Commercial_Name is used instead.

How can I write code to take the entire range, (that will grow), and IF the FullName field is empty, to automatically enter QB_File_Name in the FullName place? I'm thinking this will solve my problem? But maybe there is also something else wrong in my existing code...plz advise.

I’m not getting an error popup, but I’m not able to ADD a new client. I’m able to EDIT, but not add at all.

The message box pops up saying "Client's Full Name already exists" or “QuickBooks File Name already exists".
(Which as you can see in the code below, IS supposed to do if that’s the case, however… I’m getting the popup msg even if they do NOT exist.)

Here’s my code that is NOT working. ☹ Hoping for HELP!

Private Sub CmdButton_CONTINUE1_Click() 'When Userform "CONTINUE" Button is clicked.
 
Dim TargetRow As Integer  ‘ Variable for POSITION CONTROL
Dim FullName As String   'Variable for FULL NAME
Dim QBFileName As String   'Variable Quick Books File Name 
Dim UserMessage As String 'variable to configure user message at the end
 
FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name  'Variable configured: to be used in Msg when client is entered, shows full name
 
'begin ck if EDIT or ADD NEW Mode
If Sheets("Engine").Range("B4").Value = "NEW" Then  'in 'NEW' mode
   
'''**'BEGINS VALIDATION CHECK: IF in 'add NEW' mode to prevent duplicate FULL NAME J Column entries**''
    If Application.WorksheetFunction.CountIf(Sheets("2019_ClientDatabase").Range("J3:J4000"), FullName) > 0 Then
 
    MsgBox "Client's Full Name already exists", 0, "Check"  'notify user of existing client
    Exit Sub 'exit routine
   
    End If  'ends validation check OF Duplicate FULLNAME (J Column)
 
 
    '''**'BEGINS VALIDATION CHECK: IF in 'add NEW' mode to prevent duplicate QBFileName B Column entries**''
    If Application.WorksheetFunction.CountIf(Sheets("2019_ClientDatabase").Range("B3:B4000"), QBFileName) > 0 Then
 
    MsgBox "QuickBooks File Name already exists", 0, "Check"  'notify user of existing QB client
    Exit Sub 'exit routine
 
    End If  'ends validation check QBFileName B Column
 
 
TargetRow = Sheets("Engine").Range("B3").Value + 1 'make variable equal to COUNTA formula on worksheet + 1
UserMessage = " has been added to the database" 'configure user message for add new entry
 
Else  'in EDIT Mode
 
TargetRow = Sheets("Engine").Range("B5").Value 'make variable equal to the value saved in the engine
UserMessage = "'s details have been edited" 'configure user message for edit entry
 
End If
'end check if in 'edit' or 'add new mode'
 
 
'''****BEGIN INPUT DATA INTO DATABASE****'''
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 0).Value = Txt_QB_File_Name 'QuickBooks File Name

Also posted https://www.ozgrid.com/forum/forum/...rform-if-statements-are-not-working-correctly

 

[JDR444]

YEA! I FOUND AND FIXED AN ERROR IN MY CODE that prevented me from ADDING.

However, I'm still running into the issue with a duplicate entry if FULL NAME is not used.


Partially FIXED CODE:

Dim TargetRow As Integer '1st part of Variable for POSITION CONTROL
Dim FullName As String   'Variable for FULL NAME to be used in msg box when client is entered
Dim QBFileName As String   'Variable Quick Books File Name to be used in msg box when client is entered
Dim UserMessage As String 'variable to configure user message at the end

FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name  'Variable configured: to be used in Msg when client is entered, it will show the full name.
QBFileName = Txt_QB_File_Name

'begin ck if EDIT or ADD NEW Mode
If Sheets("Engine").Range("B4").Value = "NEW" Then  'in 'NEW' mode
    
    '''**'BEGINS VALIDATION CHECK: IF in 'add NEW' mode to prevent duplicate FULL NAME J Column entries**''
    If Application.WorksheetFunction.CountIf(Sheets("2019_ClientDatabase").Range("J3:J4000"), FullName) > 0 Then

    MsgBox "Client's Full Name already exists", 0, "Check"  'notify user of existing client
    Exit Sub 'exit the program routine
    
    End If  'ends validation check OF Duplicate FULLNAME (J Column)


    '''**'BEGINS VALIDATION CHECK: IF in 'add NEW' mode to prevent duplicate QBFileName B Column entries**''
    If Application.WorksheetFunction.CountIf(Sheets("2019_ClientDatabase").Range("B3:B4000"), QBFileName) > 0 Then

    MsgBox "QuickBooks File Name already exists", 0, "Check"  'notify user of existing client
    Exit Sub 'exit the program routine

    End If  'ends validation checkTargetRow = Sheets("Engine").Range("B3").Value + 1 'make variable equal to COUNTA formula on worksheet + 1
UserMessage = " has been added to the database" 'configure user message for add new entry

Else  'in EDIT Mode

TargetRow = Sheets("Engine").Range("B5").Value 'make variable equal to the value saved in the engine
UserMessage = "'s details have been edited" 'configure user message for edit entry

End If
'end check if in 'edit' or 'add new mode'


'''****BEGIN INPUT DATA INTO DATABASE****'''
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 0).Value = Txt_QB_File_Name 'QuickBooks File Name