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:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Re: Any help would be greatly appreciated

You have a whole load of nested ifs, but only one end if. Try it like this instead
Code: 
If OPTION_L = True Then
   Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "L" 'ALLOWS ABILTY TO SELECT YES BUTTON
ElseIf OPTION_P = True Then
   Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "P" 'ALLOWS ABILITY TO SELECT NO BUTTON
ElseIf OPTION_R = True Then
   Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "R" 'ALLOWS ABILTY TO SELECT YES BUTTON
ElseIf OPTION_S = True Then
   Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILITY TO SELECT NO BUTTON
End If
 
Upvote 0
Re: Any help would be greatly appreciated

Another option is to use a Select Case Statement like
Code: 
Dim Res As String
Select Case True
   Case OPTION_L
      Res = "L"
   Case OPTION_P
      Res = "P"
   Case OPTION_R
      Res = "R"
   Case OPTION_S
      Res = "S"
End Select
Sheets("MAIN_TABLE").Range("DATA_START").Offset(TargetRow, 11).Value = Res
 
Upvote 0
Re: Any help would be greatly appreciated

Here are some tips:
- Variable declarations are best done at the beginning of the code.
- How to use Set with a sheet to reduce code.
- How to use With on a sheet and the range to reduce code.
- 2 ways to use the IF-THEN
- Try to use indent in the code to make it more understandable.
- If you are not going to do any treatment to the variable fullname, then you can directly use textbox1, that way you save a statement and a line.


Let me know any questions with the updated code.

Code: 
Private Sub CONTINUE_BUTTON_Click() 'Name of routine
    'DECLARATION AREA
    Dim TargetRow As Integer 'Variable for position control
    Dim sh As Worksheet
    
    Set [COLOR=#0000ff]sh [/COLOR]= Sheets("ENGINE")
    
    If sh.Range("B3").Value = "NEW" Then
        If Application.WorksheetFunction.CountIf(Sheets("MAIN_TABLE").Range("A:A"), [COLOR=#0000ff]TextBox1[/COLOR]) > 0 Then
            '''IF VALIDATION CONFIRMS NAME EXISTS AND IS NOT IN EDIT MODE REFUSE ENTRY'''
            MsgBox [COLOR=#0000ff]TextBox1 [/COLOR]& " 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
    
    ''''BEGIN CODE FOR INPUT TO DATABASE''''
    [COLOR=#0000ff]With [/COLOR]Sheets("MAIN_TABLE").Range("DATA_START")
        .Offset(TargetRow, 0).Value = TextBox1
        .Offset(TargetRow, 1).Value = TextBox2
        .Offset(TargetRow, 2).Value = TextBox3
        .Offset(TargetRow, 3).Value = TextBox4
        .Offset(TargetRow, 4).Value = TextBox5
        .Offset(TargetRow, 5).Value = TextBox6
        .Offset(TargetRow, 6).Value = TextBox7
        .Offset(TargetRow, 7).Value = TextBox8
        .Offset(TargetRow, 8).Value = TextBox9
        .Offset(TargetRow, 9).Value = TextBox10
        .Offset(TargetRow, 10).Value = TextBox11
    
        'way one
        If OPTION_L = True Then
            .Offset(TargetRow, 11).Value = "L" 'ALLOWS ABILTY TO SELECT YES BUTTON
        End If
        
        'way two
        If OPTION_P = True Then .Offset(TargetRow, 11).Value = "P" 'ALLOWS ABILITY TO SELECT NO BUTTON
        
        If OPTION_R = True Then .Offset(TargetRow, 11).Value = "R" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_S = True Then .Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_GC = True Then .Offset(TargetRow, 11).Value = "GC" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_C = True Then .Offset(TargetRow, 11).Value = "C" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_PS = True Then .Offset(TargetRow, 11).Value = "PS" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_AR = True Then .Offset(TargetRow, 11).Value = "AR" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_F = True Then .Offset(TargetRow, 11).Value = "F" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_CC = True Then .Offset(TargetRow, 11).Value = "CC" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_AD = True Then .Offset(TargetRow, 11).Value = "AD" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_A = True Then .Offset(TargetRow, 11).Value = "A" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_AR = True Then .Offset(TargetRow, 11).Value = "AR" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_CC = True Then .Offset(TargetRow, 11).Value = "CC" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_GOV = True Then .Offset(TargetRow, 11).Value = "GOV" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_D = True Then .Offset(TargetRow, 11).Value = "D" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_ER = True Then .Offset(TargetRow, 11).Value = "ER" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_ES = True Then .Offset(TargetRow, 11).Value = "ES" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_LS = True Then .Offset(TargetRow, 11).Value = "LS" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_M = True Then .Offset(TargetRow, 11).Value = "M" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_PC = True Then .Offset(TargetRow, 11).Value = "PC" 'ALLOWS ABILTY TO SELECT YES BUTTON
        If OPTION_PM = True Then .Offset(TargetRow, 11).Value = "PM" 'ALLOWS ABILITY TO SELECT NO BUTTON
        If OPTION_S = True Then .Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILTY TO SELECT YES BUTTON
    End With
    
    MsgBox FullName & " has been added to the database", 0, "Complete" 'messages user that a new user has been added to the database
End Sub
 
Upvote 0
Re: Any help would be greatly appreciated

Thank you DanteAmor for the assistance with this it is greatly appreciated. I have cleaned up the code and tested it now that I have had a chance as I was not able to do so when you had responded at the time.

Please see the new code below.

Code: 
Private Sub CONTINUE_BUTTON_Click() 'Name of routine
    'DECLARATION AREA
    Dim TargetRow As Integer 'Variable for position control
    Dim sh As Worksheet
    
    Set sh = Sheets("ENGINE")
    
    If sh.Range("B3").Value = "NEW" Then
        If Application.WorksheetFunction.CountIf(Sheets("MAIN_TABLE").Range("A:A"), TextBox1) > 0 Then
            '''IF VALIDATION CONFIRMS NAME EXISTS AND IS NOT IN EDIT MODE REFUSE ENTRY'''
            MsgBox TextBox1 & " 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
    
       
    ''''BEGIN CODE FOR INPUT TO DATABASE''''
    With Sheets("MAIN_TABLE").Range("DATA_START")
        .Offset(TargetRow, 0).Value = TextBox1
        .Offset(TargetRow, 1).Value = TextBox2
        .Offset(TargetRow, 2).Value = TextBox3
        .Offset(TargetRow, 3).Value = TextBox4
        .Offset(TargetRow, 4).Value = TextBox5
        .Offset(TargetRow, 5).Value = TextBox6
        .Offset(TargetRow, 6).Value = TextBox7
        .Offset(TargetRow, 7).Value = TextBox8
        .Offset(TargetRow, 8).Value = TextBox9
        .Offset(TargetRow, 9).Value = TextBox10
        .Offset(TargetRow, 10).Value = TextBox11
    
        
        'way two
        If OPTION_L = True Then .Offset(TargetRow, 11).Value = "L" 'ALLOWS ABILITY TO SELECT LANDSCAPING BUTTON
    If OPTION_P = True Then .Offset(TargetRow, 11).Value = "P" 'ALLOWS ABILITY TO SELECT PAVING BUTTON
        If OPTION_R = True Then .Offset(TargetRow, 11).Value = "R" 'ALLOWS ABILTY TO SELECT ROOFING BUTTON
        If OPTION_S = True Then .Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILITY TO SELECT SECURITY SERVICES BUTTON
        If OPTION_GC = True Then .Offset(TargetRow, 11).Value = "GC" 'ALLOWS ABILTY TO SELECT GENERAL CONTRACTING BUTTON
        If OPTION_C = True Then .Offset(TargetRow, 11).Value = "C" 'ALLOWS ABILITY TO SELECT CONSTRUCTION BUTTON
        If OPTION_PS = True Then .Offset(TargetRow, 11).Value = "PS" 'ALLOWS ABILTY TO SELECT PROFFESIONAL SERVICES BUTTON
        If OPTION_AR = True Then .Offset(TargetRow, 11).Value = "AR" 'ALLOWS ABILITY TO SELECT AUTOMOBILE REPAIR BUTTON
        If OPTION_F = True Then .Offset(TargetRow, 11).Value = "F" 'ALLOWS ABILTY TO SELECT FLORIST BUTTON
        If OPTION_CC = True Then .Offset(TargetRow, 11).Value = "CC" 'ALLOWS ABILITY TO SELECT CAR CARE SERVICES BUTTON
        If OPTION_AD = True Then .Offset(TargetRow, 11).Value = "AD" 'ALLOWS ABILTY TO SELECT AUTOMOBILE DEALER BUTTON
        If OPTION_A = True Then .Offset(TargetRow, 11).Value = "A" 'ALLOWS ABILITY TO SELECT AVIATION BUTTON
        If OPTION_AR = True Then .Offset(TargetRow, 11).Value = "AR" 'ALLOWS ABILTY TO SELECT AUTOMOBILE REPAIR BUTTON
        If OPTION_CC = True Then .Offset(TargetRow, 11).Value = "CC" 'ALLOWS ABILITY TO SELECT CHILD CARE BUTTON
        If OPTION_GOV = True Then .Offset(TargetRow, 11).Value = "GOV" 'ALLOWS ABILTY TO SELECT CITY OWNED AND OPERATED BUTTON
        If OPTION_D = True Then .Offset(TargetRow, 11).Value = "D" 'ALLOWS ABILITY TO SELECT DEALER BUTTON
        If OPTION_ER = True Then .Offset(TargetRow, 11).Value = "ER" 'ALLOWS ABILTY TO SELECT EQUIPMENT RENTAL BUTTON
        If OPTION_ES = True Then .Offset(TargetRow, 11).Value = "ES" 'ALLOWS ABILITY TO SELECT ESSENTIAL SERVICES BUTTON
        If OPTION_LS = True Then .Offset(TargetRow, 11).Value = "LS" 'ALLOWS ABILTY TO SELECT LANDSCAPE & SNOW REMOVAL BUTTON
        If OPTION_M = True Then .Offset(TargetRow, 11).Value = "M" 'ALLOWS ABILITY TO SELECT MISCELLANIOUS BUTTON
        If OPTION_PC = True Then .Offset(TargetRow, 11).Value = "PC" 'ALLOWS ABILTY TO SELECT PARKING CONTROL BUTTON
        If OPTION_PM = True Then .Offset(TargetRow, 11).Value = "PM" 'ALLOWS ABILITY TO SELECT PROPERTY MAINTENANCE BUTTON
        If OPTION_S = True Then .Offset(TargetRow, 11).Value = "S" 'ALLOWS ABILTY TO SELECT SNOW RELATED BUTTON
    End With
      
    MsgBox FullName & " has been added to the database", 0, "Complete" 'messages user that a new user has been added to the database
End Sub


    Private Sub CANCEL_BUTTON_Click()
    Unload DATA_FORM
    End Sub

The only problem I am now getting is that I am not writing to Cell 11 for some odd reason and I am not sure why this is happening. I checked the spreadsheet and it does not seem to have any of the information for Cell 11 in any of the cells so I am not quite sure where it is posting this information. I am also having an issue with line that is posting the message that the information has been posted. Below is the line of code pertaining to the message part I am referring to.

Code: 
        If Application.WorksheetFunction.CountIf(Sheets("MAIN_TABLE").Range("A:A"), TextBox1) > 0 Then
            '''IF VALIDATION CONFIRMS NAME EXISTS AND IS NOT IN EDIT MODE REFUSE ENTRY'''
            MsgBox TextBox1 & " HAS BEEN FOUND IN DB PLEASE CONFIRM INFO ENTERED", 0, "NAME FOUND"

Thanks again in advance for the help

Kevin
 
Last edited by a moderator:
Upvote 0
Re: Any help would be greatly appreciated

Thank you Fluff for the assistance it is greatly appreciated.

I hope you had a great weekend.

Kevin
 
Upvote 0
Re: Any help would be greatly appreciated

KRKComputers said:
Thank you DanteAmor for the assistance with this it is greatly appreciated. I have cleaned up the code and tested it now that I have had a chance as I was not able to do so when you had responded at the time.

Please see the new code below.

Kevin
Click to expand...

What do you have in the "DATA_START" range?

Code: 
[COLOR=#333333]With Sheets("MAIN_TABLE").Range("DATA_START")[/COLOR]

What do you have in B2?
Code: 
sh.Range("B2").Value + 1

What do you have in B4?

Code: 
[COLOR=#333333]TargetRow = Sheets("ENGINE").Range("B4").Value[/COLOR]


Explain what you want to do.
 
Last edited:
Upvote 0
Re: Any help would be greatly appreciated

Dante,

The MAIN_TABLE spreadsheet contains data with the following headings from cell A1 - K1

Company
Contact
Address
City
Prov
Postal Code
Phone#1
Phone#2
Email
Comments
Code (used to place business based on services)

The Data Start Range is the point at which I have started to enter in the data into the spreadsheet which is A2 - K476 at the current moment in time. The code in which I am using is code that I am trying to correct and take over from someone else and I am not quite sure what they had done and am currently trying to clean it up so it will work as needed and desired. The form was rebuilt and am now attempting to do the same to this code.

For the Engine spreadsheet it is used for the last line count within the spreadsheet and the information that is contained within the cells is as follows.

B2 = =COUNTA(MAIN_TABLE!A1:A10000)
B3 = EDIT
B4 = 476

C2 = Total entries in database - maximum 10000
C3 = MODE
C4 = ROW STORE

I have created a form for the user to fill out which in turn when they click on the Continue button will display a MSGBOX that contains the information that is included in Cell A1 and display this as a confirmation that the information entered into the form has been placed into the database.

I hope the above information is adequate and helps if you do require any other info please let me know.

Thanks in advance
Kevin
 
Last edited by a moderator:
Upvote 0
Re: Any help would be greatly appreciated

Try this.

Replace this
Code: 
    With Sheets("MAIN_TABLE").Range("DATA_START")
        .Offset(TargetRow, 0).Value = TextBox1
        .Offset(TargetRow, 1).Value = TextBox2
        .Offset(TargetRow, 2).Value = TextBox3
        .Offset(TargetRow, 3).Value = TextBox4
        .Offset(TargetRow, 4).Value = TextBox5
        .Offset(TargetRow, 5).Value = TextBox6
        .Offset(TargetRow, 6).Value = TextBox7
        .Offset(TargetRow, 7).Value = TextBox8
        .Offset(TargetRow, 8).Value = TextBox9
        .Offset(TargetRow, 9).Value = TextBox10
        .Offset(TargetRow, 10).Value = TextBox11


By:
Code: 
    ''''BEGIN CODE FOR INPUT TO DATABASE''''    With Sheets("MAIN_TABLE")
        .cells(TargetRow, 0).Value = TextBox1
        .cells(TargetRow, 1).Value = TextBox2
        .cells(TargetRow, 2).Value = TextBox3
        .cells(TargetRow, 3).Value = TextBox4
        .cells(TargetRow, 4).Value = TextBox5
        .cells(TargetRow, 5).Value = TextBox6
        .cells(TargetRow, 6).Value = TextBox7
        .cells(TargetRow, 7).Value = TextBox8
        .cells(TargetRow, 8).Value = TextBox9
        .cells(TargetRow, 9).Value = TextBox10
        .cells(TargetRow, 10).Value = TextBox11
 
Last edited by a moderator:
Upvote 0
And this
Code: 
[COLOR=#333333]If OPTION_P = True Then .Offset(TargetRow, 11).Value = "P" 'ALLOWS ABILITY TO SELECT NO BUTTON[/COLOR]

By:
Code: 
[COLOR=#333333]If OPTION_P = True Then .Cells(TargetRow, 11).Value = "P" 'ALLOWS ABILITY TO SELECT NO BUTTON[/COLOR]


But you must select one of the optionbutton
 
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,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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