Submit Button - VBA Code

[SoyuzGRU]

I'm creating a User Form that will basically automate user input into an Excel Sheet for my organization. I have 3 Sheets on this Workbook labelled: Menu, Master and Data Validation. The Menu sheet allows users to select any forms available (connected through macros), the Master sheet saves all information that is input from sheets and Data Validation sheet is connected to some combo boxes available in the forms for easy updating.

Upon clicking submit on the User Form I always received error messages, even having tried multiple VBA codes and trying to make sense of these. Basically what I want is all the information from the User Form after clicking a submit button to be input into the Master sheet at the next available empty row. Any help is appreciated in advanced and below you will find my current code for my submit button:



Private Sub CommandButton1_Click()



Dim LR As Long 'LR = Last Row

Sheet1.Activate 'Activates the database sheet

LR = Sheet1.Cells(Rows.Count, 1).End(x1Up).Row

With Sheet1 'Each part of the form submitted into a separate column

.Range("A" & LR).Value = ComboBox1.Value
.Range("B" & LR).Value = ComboBox2.Value
.Range("C" & LR).Value = ComboBox3.Value
.Range("D" & LR).Value = TextBox1.Value
.Range("E" & LR).Value = TextBox2.Value
.Range("F" & LR).Value = TextBox3.Value
.Range("G" & LR).Value = TextBox4.Value
.Range("H" & LR).Value = ComboBox4.Value
.Range("I" & LR).Value = ComboBox5.Value
.Range("J" & LR).Value = TextBox5.Value
.Range("K" & LR).Value = ComboBox6.Value

End With 'Resets form to blank state after submitting

Me.ComboBox1.Value = Null
Me.ComboBox2.Value = Null
Me.ComboBox3.Value = Null
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox4.Value = Null
Me.ComboBox5.Value = Null
Me.TextBox5.Value = ""
Me.ComboBox6.Value = Null

End Sub

 

[6StringJazzer]

First, there is no need to active Sheet1. You can remove that line of code.

Second, LR is the last row. You want to write in the row after the last row, so use this:

LR = Sheet1.Cells(Rows.Count, 1).End(x1Up).Row + 1

Also,

I always received error messages

Whenever you are asking for help with error messages, always tell us what error message you got, and what line of code is highlighted when you click Debug. I see nothing that will give you a runtime error, if all of your control references are valid.

 

[My Aswer Is This]

Try this it works for me.
I always like to use sheet names, so I change the script to run on sheet named "Master"
Modify this to your needs
Using Sheet1 makes it complicated for me.

Private Sub CommandButton1_Click()
'Modified  2/17/2022  3:46:30 PM  EST
Dim LR As Long
LR = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    With Sheets("Master")
        .Range("A" & LR).Value = ComboBox1.Value
        .Range("B" & LR).Value = ComboBox2.Value
        .Range("C" & LR).Value = ComboBox3.Value
        .Range("D" & LR).Value = TextBox1.Value
        .Range("E" & LR).Value = TextBox2.Value
        .Range("F" & LR).Value = TextBox3.Value
        .Range("G" & LR).Value = TextBox4.Value
        .Range("H" & LR).Value = ComboBox4.Value
        .Range("I" & LR).Value = ComboBox5.Value
        .Range("J" & LR).Value = TextBox5.Value
        .Range("K" & LR).Value = ComboBox6.Value

End With 'Resets form to blank state after submitting

Me.ComboBox1.Value = Null
Me.ComboBox2.Value = Null
Me.ComboBox3.Value = Null
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox4.Value = Null
Me.ComboBox5.Value = Null
Me.TextBox5.Value = ""
Me.ComboBox6.Value = Null

End Sub

 

[My Aswer Is This]

To clear ll your controls you can use this code:

'Clear all controls
Dim ctrl As MSForms.Control
For Each ctrl In Me.Controls
    Select Case True
        Case TypeOf ctrl Is MSForms.CheckBox
            ctrl.Value = False
        Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.ComboBox
            ctrl.Value = ""
    End Select
    
Next ctrl

 

[6StringJazzer]

Using Sheet1 makes it complicated for me.

It is a best practice to use the codename to refer to a sheet statically because the user can change the name on the tab and Worksheets("Master") will stop working. Either way, that is not what is causing the problem in the OP.

 

[GWteB]

Hi @SoyuzGRU, welcome to MrExcel.
You're most likely encountering a compile error.
If that's the case then you have probably fallen into the trap of an erroneous copy/paste.
Hard to spot within the code you posted, nevertheless it's there: a typo!

 

[SoyuzGRU]

First, there is no need to active Sheet1. You can remove that line of code.

Second, LR is the last row. You want to write in the row after the last row, so use this:

LR = Sheet1.Cells(Rows.Count, 1).End(x1Up).Row + 1

Also,

Whenever you are asking for help with error messages, always tell us what error message you got, and what line of code is highlighted when you click Debug. I see nothing that will give you a runtime error, if all of your control references are valid.

I tried this solution and still seem to be receiving an error message.

My apologies for not posting the previous error code, I'm a little new to this, but it seems to be the same: error 1004.

Once I hit debug it just seems to highlight the following:

LR = SHeet1.Cells(Rows.Count, 1).End(x1Up).Row + 1

I've also tried the other possible solutions in the post and seem to be having the same problem. If at all possible, I want to refrain from using any information that could be changed in the coding such as referring to the Sheet as "Master" as this project is being completed for another individual who isn't familiar with how to modify VBA coding.

Thanks

 

[SoyuzGRU]

Hi @SoyuzGRU, welcome to MrExcel.
You're most likely encountering a compile error.
If that's the case then you have probably fallen into the trap of an erroneous copy/paste.
Hard to spot within the code you posted, nevertheless it's there: a typo!

View attachment 58174

Is the solution removing the 1?

I'm not familiar with the (x1Up) , but from your post does that mean I have to remove the 1 from the code?

 

[GWteB]

You need to replace the 1 with a letter L ....

EDIT:
so (x1Up) should have been (xlUp)

 

[My Aswer Is This]

Maybe you do not have a Sheet1
Maybe sheet1 has been deleted