UserForm to enter Orders

[excel0429]

Would like to reach out to this community to ask for help. I have been creating some user forms in Excel but I am stuck when it come to creating user form to enter customer orders. Any recommendation would be greatly appreciated.

User Form would generate a SO auto number 1234, however would have an option for multiple lines for order 1234 the data page would look like:

CustomeR NAME SO# LINE # PART# PRICE
XYZ 1234 1 IRON SUP 10
XYZ 1234 2 BEE 3
XYZ 1234 3 SOUP 5
XYZ 1234 4 VD 10

 

[Logit]

Re: Help With UserForm to enter Orders

.
Here is a basic code layout for creating a database ... which is what you are seeking.

Mind you ... this project is not complete for your fields, names, locations, etc. Study the
layout and edit the code to fit your purposes. Great learning experience !

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    'Col A = 1; Col B = 2; Col C = 3; Col D = 4; Col E = 5; Col F = 6; Col g = 7, etc. etc.
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Text
    ws.Cells(newRow, 2).Value = Me.txtSurname.Text
    
    'continue the remainder adhering to the pattern seen above.
    
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/kRUx3Q2WQ1QLAupvEtfQE58THEdjX8OxCaLktVjgP3g

 

[excel0429]

Re: Help With UserForm to enter Orders

Thank you for this post, still unclear on how to use the ' comment out, will this generate a new line order each time I create an order number? the workbook I downloaded did not lay out the repeating of the order for each order number.

 

[Logit]

Re: Help With UserForm to enter Orders

.

 'Col A = 1; Col B = 2; Col C = 3; Col D = 4; Col E = 5; Col F = 6; Col g = 7, etc. etc.
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Text
    ws.Cells(newRow, 2).Value = Me.txtSurname.Text

ws.Cells(newRow, 1) ... the number One after "newRow" is equal to the column A.

ws.Cells(newRow, 2) ... the number Two after "newRow" is equal to the column B.


Refer to the commented line at top ... you can see where 1 = Col A; 2 = Col B; 3 = Col C, etc. etc.



This : ws.Cells(newRow, 1) translates to :

ws (defined at the top of the macro as the ActiveSheet)

Cells(newRow, 1) translates to the newRow, Col A.



The "newRow", using the macro provided, will always be the first blank row after the last used row in Col A. That is defined with this line :

newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

newRow generates the data to populate to the new row ... first blank row below the last used row.


Referring to this portion :

ws.Cells(newRow, 1).Value = Me.txtFirstName.Text

The text entered into the UserForm textbox named txtFirstName, will be populated to the first blank cell in Col A.


ws.Cells(newRow, 2).Value = Me.txtSurname.Text

The text entered into the UserForm textbox named txtSurname will be populated to the first blank cell in Col B.



Edit the UserForm to fit your needs for your ordering system. Add more textboxes to accommodate all the different fields you laid out in your original post.
For example :

Customer Name .... you could name a UserForm textbox as txtCustName

SO# ... you could name a UserForm textbox as txtSONum

LINE # ... you could name a UserForm textbox as txtLineNum

PART# ... you could name a UserForm textbox as txtPartNum

PRICE ... you could name a UserForm textbox as txtPrice

You have five fields, so you will need five text boxes.

Customer Name = Col A
So # = Col B
Line # = Col C
Part # = Col D
Price # = Col E

 

[excel0429]

Re: Help With UserForm to enter Orders

Thank you so much, I will give this a try...

 

[excel0429]

Re: Help With UserForm to enter Orders

thanks again but the code is no yielding he desired out come. I have one form that contains an auto populate field for the sales number and multiple text boxes to populate the line numbers with different information cannot seems to get the data in a database format to excel workbook

 

[Logit]

.
Post your code.

 

[excel0429]

What I did was to include an if statements and it works not ideal, need to see how I can edit this code.. Will upload when I figure out how to do so

 

[Logit]

This forum doesn't have provision for posting code directly. You will need to post to a cloud site (DropBox.com, Google Cloud, Amazone Cloud, etc) and
provide a link to the file.

Glad you have it working now.