Missing End If error

[KRKComputers]

Hello,

I do not consider myself to be a pro at writing the VB code but I am trying and any help that anyone can give me in regards to the following lines of code would be greatly appreciated.

When I attempt to run the code I keep getting a End If missing and I have been going crazy trying to find out where in the code this is coming from and am hoping someone can point out where this mistake is for me so I may be able to correct it or if you feel that this code is bulky and think I can clean it up a bit that help would also be appreciated. When giving me pointers for this please keep in mind I am trying to learn and am by no means an expert and would need to be directed to what lines do need fixing.

====
Code Below
====

Private Sub CANCEL_BUTTON_Click()
Unload DATA_FORM
End Sub


Private Sub CONTINUE_BUTTON_Click() 'Name of routine


MsgBox "THIS FORM WILL NOW SAVE THE INFORMATION AND CLOSE! CLICK OK TO CONTINUE", 0, "INFORMATION" 'Provides a visual reference so you can see what is happening
Dim TargetRow As Integer 'Variable for position control


If Sheets("ENGINE").Range("B3").Value = "NEW" Then
TargetRow = Sheets("ENGINE").Range("B2").Value + 1 'Variable for TARGETROW to be used in other areas
Else
TargetRow = Sheets("ENGINE").Range("B4").Value 'VARIABLE IN PLACE FOR EDIT MODE
End If


Dim FullName As String 'FullName variable
FullName = TextBox1 'Variable for FULLNAME


If Sheets("ENGINE").Range("B3").Value = "NEW" Then
'''BEGIN VALIDATION CHECK TO CONFIRM IF NAME EXISTS'''
If Application.WorksheetFunction.CountIf(Sheets("MAIN_TABLE").Range("A2:A10000"), FullName) > 0 Then
'''IF VALIDATION CONFIRMS NAME EXISTS AND IS NOT IN EDIT MODE REFUSE ENTRY'''
MsgBox FullName & " HAS BEEN FOUND IN DB PLEASE CONFIRM INFO ENTERED", 0, "NAME FOUND"
Exit Sub
    End If
End If


''''BEGIN CODE FOR INPUT TO DATABASE''''
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 0).Value = TextBox1
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 1).Value = TextBox2
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 2).Value = TextBox3
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 3).Value = TextBox4
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 4).Value = TextBox5
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 5).Value = TextBox6
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 6).Value = TextBox7
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 7).Value = TextBox8
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 8).Value = TextBox9
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 9).Value = TextBox10
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 10).Value = TextBox11
'Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = TXTBX_UNLCKD
'Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 12).Value = TXTBX_GOGLCK
'Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 13).Value = TXTBX_APLLCK
'Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 14).Value = OPTION_YES
'Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 14).Value = OPTION_NO
'Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 15).Value = TXTBX_CMNTS


If OPTION_L = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "L" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
 If OPTION_P = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "P" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_R = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "R" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_S = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_GC = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "GC" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_C = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "C" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_PS = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "PS" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_AR = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "AR" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_F = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "F" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_CC = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "CC" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_AD = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "AD" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_A = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "A" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_AR = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "AR" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_CC = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "CC" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_GOV = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "GOV" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_D = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "D" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_ER = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "ER" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_ES = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "ES" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_LS = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "LS" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_M = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "M" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_PC = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "PC" 'ALLOWS ABILTY TO SELECT YES BUTTON
Else
If OPTION_PM = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "PM" 'ALLOWS ABILITY TO SELECT NO BUTTON
Else
If OPTION_S = True Then
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILTY TO SELECT YES BUTTON
End If
''''END CODE FOR INPUT TO DATABASE''''


'Unload DATA_FORM 'Closes the userform


MsgBox FullName & " has been added to the database", 0, "Complete" 'messages user that a new user has been added to the database
'==========================================================


End Sub

====
End Code
====

Thanks for any help in advance

Kevin

 

[KRKComputers]

Dante,

Thank you very much this worked after I found that I had a couple of other mistakes and resolved those and now items are writing to the spreadsheet.

One other question if I may get your help with it is that the last line code is not moving to the next line for me and is staying on line 476 only. would you happen to have any idea as to why this is happening based on the information that I have given you in the past.


If you do require more info feel free to let me know.

Thank you very much for the help

Thanks in advance
Kevin

 

[DanteAmor]

For your comments the 476 is in cell B4 but nowhere in your code do you update cell B4.
You must have more code in your userform.

 

[KRKComputers]

Dante,

The engine sheet does update the count in cell B2 but does not update Cell B4. I tried to change the code below and still get the same issue, I am now able to display the MSGBOX information contained in Textbox1 the MSGBOX does pop up and is grabbing the info from Textbox1 as needed. I am just unable to get the last line entry to continue as needed.

If you have any ideas on how I can accomplish this it would be greatly appreciated. The lines of code are what i have to accomplish this along with the information contained on the engine sheet.

    Dim sh As Worksheet    
    Set sh = Sheets("ENGINE")
    
    If sh.Range("B3").Value = "NEW" Then
        If Application.WorksheetFunction.CountIf(Sheets("MAIN_TABLE").Range("A2:A10000:"), TextBox1) > 0 Then
            '''IF VALIDATION CONFIRMS NAME EXISTS AND IS NOT IN EDIT MODE REFUSE ENTRY'''
            MsgBox TextBox1 & " USER HAS BEEN FOUND IN DB PLEASE CONFIRM INFO ENTERED", 0, "NAME FOUND"
            Exit Sub
            
        End If
        TargetRow = sh.Range("B2").Value + 1 'Variable for TARGETROW to be used in other areas
    Else
        TargetRow = sh.Range("B4").Value 'VARIABLE IN PLACE FOR EDIT MODE
    End If

Thanks in advance
Kevin

 

[DanteAmor]

I suggest you remove the original design and create a new, more functional design that you can understand.


I recommend that you do not have in the cells the row number that you are going to update, that control can be done perfectly by the macro.

If you want I can help you create all the code. Create a new thread where you explain in detail what you require, with examples of your sheet and your form. Explain with as much detail as possible, that way it will be easier to help you.

 

[KRKComputers]

Dante,

I almost have it completed and this is the last piece of the puzzle that I am looking to get done.

I thank you kindly for your help up till this point and will keep pushing forward and see what I can come up with. If it comes down to the point that I need to re-write the entire spreadsheet I will try to get in touch with you as suggested for some assistance as I do not want to be a pest and I do truly appreciate the help up to this point. I was just hoping that you might be able to see what is messed up in the code based on past info, I completely understand it is not easy for someone to help when they have limited info available to them.

Thanks again for all your help
Kevin

 

[DanteAmor]

It is difficult to help you without the data or the sheets.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[KRKComputers]

Dante,

Once again I appreciate your help with this issues and have attached a link tot he Dropbox file.

https://www.dropbox.com/s/ru3ake2cmowduh8/Spreadsheet Share.xlsm?dl=0

If you have any issues obtaining the file please let me know and I will try to correct as fast as possible for you.

Thanks in advance
Kevin

 

[DanteAmor]

I'm sorry, but I do not understand what you want me to help you with.
You can explain how it works, which sheet I go to, which form, which macro.


Explain in detail and with examples, especially with examples, use the data you sent me in the file to put together the explanation. If examples, it is not easy to understand. So make explanations with examples.

 

[KRKComputers]

Dante,

My apologies as it appears that the information did not get placed into the message with the link. I also apologize for the delay in getting back to you as today has been a crazy day in the IT world to say the least.

I will get back to you once again with that info by end of this evening and I just wanted to respond so that you know I did get your response and that I did attempt to attach the info.

Have a great day.

Thanks in advance
Kevin

 

[DanteAmor]

Okay, let's wait for the tempest to subside.

Also, you have a great day.