Insert Row based on User Selected Row#

[MrACED]

Good Morning MREXCEL.
I have a Master list of Job#'s that tracks our internal Job#s, customers, due dates, purchase order numbers etc.

We receive PO's and enter them as internal Job#s on this list. I am trying to create a user form activated by a command button called "Add PO."

My first question: Is there a way to allow the user to select the rownumber of the worksheet and then insert the user form data above the user selected row?

Our default for new entry's is to insert data at the top of the list which keeps most recent info at the top, we like this method versus a sort because often we add PO's to Job numbers that come in across a few weeks or months so we need the user to have the ability to input the desired rownumber of the sheet via input box (textbox) and then have that PO line be inserted above the selected row.

Thanks, I can provide some current examples of the spreadsheet if needed.

 

[mumps]

Could you upload a copy of your file to a free site such as www.box.com or 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.

 

[MrACED]

Could you upload a copy of your file to a free site such as www.box.com or 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.

I will work on doing this this weekend, I am currently in the process of building/finalizing the user form... Thanks @mumps!

 

[MrACED]

So here is a Drop box link to an example Excel file....Note I have just formatted the Button and formatted the user form, I could use some help with the coding of getting this user form to work correctly.
Book1 JLM.xlsm

The Flow of this worksheet.... is Adding P.O.(s) that we receive and applying internal Job#s to them.
Sometimes we Add P.O.s to existing Jobs and sometimes we add P.O.s as New Job Numbers at the Top of the list Down which keeps most recent entries on top. So I would like the user to be able to select the Row# of the worksheet that the information get's placed on.

In the picture below, let's say the user wanted to add a P.O. with 3 items to Job #3511. They would want to place it On top of the other #3511 P.O. #s so, the Row# they select would be #4 of the worksheet and then when they add it all the other rows shift down the list. Does that make sense? Alternatively If the User wanted to add the P.O. to a New Job# They would always enter Row#3 from the worksheet and when they add it all other rows would shift down the list. I appreciate any help in making this user form work as I lack the depth of VBA coding skills to get it functioning.

In my opinion User forms are not necessarily faster or better than manual editing and entries but they do allow us an opportunity to build in some error proofing through coding as we are experiencing functioning issues with user data entry errors that are inherently a problem to manual data entry.

 

[mumps]

Click here to download your file. Click the "Add P.O." button on your sheet. Please note that I have added boxes for up to 7 item descriptions. More can be added if needed but the code would have to be modified slightly. What would be the maximum number of items you would ever need?
This is the macro in the userform code module:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim x As Long, descr As String
    With Sheets("Sheet1")
        .Rows(TextBox1.Value).Insert
        .Range("A" & TextBox1.Value).Resize(, 14).Borders.LineStyle = xlContinuous
        .Range("A" & TextBox1.Value).Resize(, 4) = Array(TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
        For x = 12 To 18
            descr = descr & Me.Controls("TextBox" & x).Value & Chr(10)
        Next x
        .Range("E" & TextBox1.Value) = descr
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "(^\n+|\n+$)"
            Sheets("Sheet1").Range("E" & TextBox1.Value) = .Replace(Sheets("Sheet1").Range("E" & TextBox1.Value), "")
        End With
        .Range("F" & TextBox1.Value).Resize(, 4) = Array(TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value)
        .Range("K" & TextBox1.Value).Resize(, 2) = Array(TextBox10.Value, TextBox11.Value)
    End With
    Unload Me
    Application.ScreenUpdating = True
End Sub

 

[MrACED]

Thanks for the quick response mumps!

Seems to function great for how I described!
I understand the complexity of making the Items box dynamic, Maybe if we provided 10 Item Rows for Now? That would probably be fine.
Let me know if this seems like too much work or too many tweaks haha!

Just a couple small FORMATTING tweaks...
In the picture below you can see I added Row #5 and a New Row#3, and Row#10
It appears the default automatic formatting is matching from the Row above whatever row# your adding.
+ Row #5 appears to have automatically formatted itself correctly when I clicked "Add-PO"
+ Row #3 (New Job#) However seems to automatically format like Row#2, (Bold&Center) also columns K&L are formatted as dates, I need them as general for listing Hours. is there a way to correct this?
1.) Maybe It's easier to set the default cell formats for each column? is this possible using VBA so it's the same every time? The default format is Upper-Left Justified (not Bold) Column A is Center-Center Justification and then Columns J thru L are Upper-Right Justification. If possible to set Columns A and H as default red text color that would be great to... Red alerts us that it still needs to be hyperlinked.

And I forgot to list the "Dollar Amount" Column J in the User Form... could you code this in?

A couple other things maybe a little more advanced...?
1.) Is there a way for excel to run spell checking within the user form? For user input text boxes? where a Red squiggly line would appear?
2.) Can we have the default P.O. Entered Date to be Today() so that they don't have to always enter that manually?
3.) When you have all data entered and click "Add P.O." Is it possible to have the form stay open for the next P.O. Entry and possibly have a message box Pop up and say "P.O.#_________ added to Job#_______ on Row#_____" as a confirmation to the user?
4.) Or instead maybe as a ticker at the bottom of the user form always display the last P.O. Entered as a reference point for the user? Because he/she could be entering a stack of P.O.s at a time. "Last Entry: P.O.#_________ added to Job#_______ on Row#_____"

I'm open to other thoughts on ways to eliminate human error as well. I've thought about having lists of customers and end users that we could update from time to time but that would help only allow certain entries if it matches the list.

Thanks Mumps, Like I said Let me know when you get board of this challenge haha, I have other buttons in mind too haha!

 

[mumps]

Click here for your file. I have added the extra item boxes, the message and a clear button. It might be easier to manually format you rows and columns to suit your needs. I've added a spell check for range B:E.

 

[MrACED]

Yes, I agree some formatting will still have to be done manually, but I was hoping to at least set the default formatting for new entries so the user didn't have to edit the format of every single entry they'll have to make. Spell check should be great!

Can you possibly add Column J - "Dollar Amount" into my user form? Under P.O. Entry date and above D-HRS?

Also, when I click - Clear form it deletes the default Today() date and then has run errors when trying to fill out the next one. Is it possible to eliminate the "Clear form button" and just have the form clear and set focus on the first textbox when clicking "Add PO?" Instead of having a separate button?

 

[mumps]

Click here for your file. I have done some manual cell formatting and also some programmatic cell formatting.

 

[MrACED]

Click here for your file. I have done some manual cell formatting and also some programmatic cell formatting.

Fantastic, It works great, even row#3 formats correctly and red text in desired cells is perfect!

Let me know if you have had enough haha there are other buttons I am looking to add for other functions too