Missing End If error

K

KRKComputers

New Member
Joined
Nov 10, 2017
Messages
43
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
====
Code: 
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
 
Last edited by a moderator:
Sort by date Sort by votes
Dante,

I hope all is well and apologize for not getting back to you but I took your advice and wrote the code again but this time made it cleaner and it is now working other then one item.

When I open my form and do a SEARCH the search will come up just fine and all is good however when I click on delete within the form the first row is getting deleted within the spreadsheet and it will keep doing this each time. I do know that the code states currentrow, 1 but it should be removing the item that I have selected and it is not. can you possibly tell me what I have done wrong so I can correct it.

Code sample below: (issue is in code for DELETE button section)

Code: 
Dim currentrow As Long





' ======================================================================================
' Below this line is the start of the section for the DELETE button within the Userform |
' ======================================================================================
' ======================
' Code for DELETE button
' ======================
Private Sub CmdDelete_Click()
answer = MsgBox("Are you sure you want to DELETE this record?", vbYesNo + vbQuestion, "Delete Record")
        If answer = vbYes Then
        Cells(currentrow, 1).EntireRow.Delete
    End If
End Sub
' ====================================================================================
' Above this line is the end of the section for the DELETE button within the Userform |
' ====================================================================================


' ====================================================================================
' Below this line is the start of the section for the EXIT button within the Userform |
' ====================================================================================
' ====================
' Code for EXIT button
' ====================
Private Sub CmdExit_Click()
    Unload MississaugaForm
    MsgBox "Mississauga Entry Form will now exit."
End Sub
' ==================================================================================
' Above this line is the end of the section for the EXIT button within the Userform |
' ==================================================================================


' ======================================================================================
' Below this line is the start of the section for the UPDATE button within the Userform |
' ======================================================================================
' ======================
' Code for UPDATE button
' ======================
Private Sub CmdUpdate_Click()
answer = MsgBox("Are you sure you want to update the record?", vbYesNo + vbQuestion, "Update Record")
If answer = vbYes Then
Cells(currentrow, 1) = TextBox1.Text
Cells(currentrow, 2) = TextBox2.Text
Cells(currentrow, 3) = TextBox3.Text
Cells(currentrow, 4) = TextBox4.Text
Cells(currentrow, 5) = TextBox5.Text
Cells(currentrow, 6) = TextBox6.Text
Cells(currentrow, 7) = TextBox7.Text
Cells(currentrow, 8) = TextBox8.Text
Cells(currentrow, 9) = TextBox9.Text
Cells(currentrow, 10) = TextBox10.Text
Cells(currentrow, 11) = TextBox11.Value
End If


End Sub
' ====================================================================================
' Above this line is the end of the section for the UPDATE button within the Userform |
' ====================================================================================


' ===========================================================================================
' Below this line is the start of the section for the CLEAR & ADD button within the Userform |
' ===========================================================================================
' ===================
' Code for ADD button
' ===================
Private Sub CmdADD_Click()
Dim lastrow As Long, count As Long
    MsgBox "User has been Added to the Spreadsheet."
lastrow = Sheet1.Cells(Rows.count, 1).End(xlUp).Row
lastrow = lastrow + 1
Cells(lastrow, 1) = TextBox1
count = 0
For i = 2 To lastrow
    If TextBox1 = Cells(i, 1) Then
    count = count + 1
    End If
    
    If count > 1 Then
        Cells(lastrow, 1) = ""
        Cells(lastrow, 2) = ""
        Cells(lastrow, 3) = ""
        Cells(lastrow, 4) = ""
        Cells(lastrow, 5) = ""
        Cells(lastrow, 6) = ""
        Cells(lastrow, 7) = ""
        Cells(lastrow, 8) = ""
        Cells(lastrow, 9) = ""
        Cells(lastrow, 10) = ""
        Cells(lastrow, 11) = ""
        MsgBox "Duplicate Entry Found name already exists!"
        End If
    If count = 1 Then
    Cells(lastrow, 1) = TextBox1.Text
    Cells(lastrow, 2) = TextBox2.Text
    Cells(lastrow, 3) = TextBox3.Text
    Cells(lastrow, 4) = TextBox4.Text
    Cells(lastrow, 5) = TextBox5.Text
    Cells(lastrow, 6) = TextBox6.Text
    Cells(lastrow, 7) = TextBox7.Text
    Cells(lastrow, 8) = TextBox8.Text
    Cells(lastrow, 9) = TextBox9.Text
    Cells(lastrow, 10) = TextBox10.Text
    Cells(lastrow, 11) = TextBox11.Value
    End If
Next
    
End Sub


' =====================
' Code for CLEAR button
' =====================
Private Sub cmdClear_Click()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
Dim ctl As Control


For Each ctl In MississaugaForm.Controls
    If TypeName(ctl) = "TextBox" Then
    ctl.Value = ""
    End If
Next ctl
    
End Sub
' =========================================================================================
' Above this line is the end of the section for the CLEAR & ADD button within the Userform |
' =========================================================================================


' ===========================================================================================
' Below this line is the start of the section for the BACK & NEXT button within the Userform |
' ===========================================================================================
' ====================
' Code for BACK Button
' ====================
Private Sub CmdBack_Click()
If currentrow = 2 Then
    MsgBox "You are currently at the first row of data within the Spreadsheet!"
    Exit Sub
End If


currentrow = currentrow - 1


TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
TextBox4 = Cells(currentrow, 4)
TextBox5 = Cells(currentrow, 5)
TextBox6 = Cells(currentrow, 6)
TextBox7 = Cells(currentrow, 7)
TextBox8 = Cells(currentrow, 8)
TextBox9 = Cells(currentrow, 9)
TextBox10 = Cells(currentrow, 10)
TextBox11 = Cells(currentrow, 11)


End Sub


' ====================
' Code for NEXT BUTTON
' ====================
Private Sub CmdNext_Click()
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.count, 1).End(xlUp).Row


If currentrow = lastrow Then
    MsgBox "You are currently viewing the last item in the spreadsheet!"
    Exit Sub
End If


currentrow = currentrow + 1


TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
TextBox4 = Cells(currentrow, 4)
TextBox5 = Cells(currentrow, 5)
TextBox6 = Cells(currentrow, 6)
TextBox7 = Cells(currentrow, 7)
TextBox8 = Cells(currentrow, 8)
TextBox9 = Cells(currentrow, 9)
TextBox10 = Cells(currentrow, 10)
TextBox11 = Cells(currentrow, 11)


End Sub
' ===========================================================================================
' Above this line is the start of the section for the NEXT & BACK button within the Userform |
' ===========================================================================================


' ===================================================================================================
' Below this line is the start of the section for the SEARCH & INTIALIZE feature within the Userform |
' ===================================================================================================
' ============================
' Code for USERFORM INITIALIZE
' ============================
Private Sub UserForm_Initialize()
currentrow = 2
TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)
TextBox4 = Cells(currentrow, 4)
TextBox5 = Cells(currentrow, 5)
TextBox6 = Cells(currentrow, 6)
TextBox7 = Cells(currentrow, 7)
TextBox8 = Cells(currentrow, 8)
TextBox9 = Cells(currentrow, 9)
TextBox10 = Cells(currentrow, 10)
TextBox11 = Cells(currentrow, 11)


End Sub
' ======================
' Code for SEARCH BUTTON
' ======================
Private Sub CmdSearch_Click()
Dim totRows As Long, i As Long


totRows = Worksheets("Mississauga").Range("A2").CurrentRegion.Rows.count


For i = 2 To totRows
    If Trim(Sheet1.Cells(i, 1)) = Trim(TextBox1.Text) Then
        TextBox1.Text = Sheet1.Cells(i, 1)
        TextBox2.Text = Sheet1.Cells(i, 2)
        TextBox3.Text = Sheet1.Cells(i, 3)
        TextBox4.Text = Sheet1.Cells(i, 4)
        TextBox5.Text = Sheet1.Cells(i, 5)
        TextBox6.Text = Sheet1.Cells(i, 6)
        TextBox7.Text = Sheet1.Cells(i, 7)
        TextBox8.Text = Sheet1.Cells(i, 8)
        TextBox9.Text = Sheet1.Cells(i, 9)
        TextBox10.Text = Sheet1.Cells(i, 10)
        TextBox11.Text = Sheet1.Cells(i, 11)
        Exit For
    End If
Next i


End Sub
' ==================================================================
' Above This line is the End of the SEARCH section for the userform |
' ==================================================================

Thanks in advance
Kevin
 
Last edited by a moderator:
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
KRKComputers said:
Dante,

I hope all is well and apologize for not getting back to you but I took your advice and wrote the code again but this time made it cleaner and it is now working other then one item.

When I open my form and do a SEARCH the search will come up just fine and all is good however when I click on delete within the form the first row is getting deleted within the spreadsheet and it will keep doing this each time. I do know that the code states currentrow, 1 but it should be removing the item that I have selected and it is not. can you possibly tell me what I have done wrong so I can correct it.

Code sample below: (issue is in code for DELETE button section)

Code: 
' Code for DELETE button
' ======================
Private Sub CmdDelete_Click()
answer = MsgBox("Are you sure you want to DELETE this record?", vbYesNo + vbQuestion, "Delete Record")
        If answer = vbYes Then
         [COLOR=#ff0000]Rows(ActiveCell.Row).Delete[/COLOR]
    End If
End Sub
' ====================================================================================
' Above this line is the end of the section for the DELETE button within the Userform |
' ====================================================================================

Thanks in advance
Kevin
Click to expand...

I do not know what you have in the variable "currentrow", but if you have already selected the cell of the row that you want to delete then it can be written in red.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
populating the next available row in a shared workbook
Replies
1
Views
420
rondeondo
rondeondo
B
  • Question Question
Populating a worksheet based on a Combo Box value within a User Form
2 3 4
Replies
35
Views
2K
igold
igold
R
  • Solved
If Textbox.Value, And If TextBox.Value Is not, Else
Replies
3
Views
694
Peter_SSs
Peter_SSs
C
  • Question Question
Excel Userform VBA - Value from Last Textbox not showing
Replies
3
Views
1K
dmt32
D
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
314
bdt
B

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top