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]

Glad it worked out. If your additional questions are not related to your original question in this thread, it is probably best to start a new thread.

 

[MrACED]

I Guess one other thing related to this thread.
It appears that my D-Hrs and B-hrs still format as dates when entered in row#3.

Is this fixable? Just need to display Hours to XX.X

 

[mumps]

Click here for your file.

 

[MrACED]

Mumps,

It appears the Dollar amount Column J Is listing as a general format.
Can you adjust so it always appears as a Currency with no decimals like the other entries? and Top-Left Justification?
Also is it possible to have Column F "On-Site Date always have double borders on the left and right sides?

Also,
I am working on another user form that relates to this same thread of picking a row number, will update you when I have that completed.

 

[mumps]

Try:

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 22
            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(, 7) = Array(TextBox6.Value, TextBox7.Value, TextBox8.Value, TextBox9.Value, TextBox10.Value, TextBox11.Value, TextBox12.Value)
    End With
    With Range("A" & TextBox1.Value)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = False
        .Font.Color = vbRed
    End With
    With Range("F" & TextBox1.Value)
        .Borders(xlEdgeLeft).LineStyle = xlDouble
        .Borders(xlEdgeRight).LineStyle = xlDouble
    End With
    Range("H" & TextBox1.Value).Font.Color = vbRed
    With Range("B" & TextBox1.Value & ":O" & TextBox1.Value)
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Font.Bold = False
    End With
    With Range("J" & TextBox1.Value)
        .NumberFormat = "$#,##0"
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlTop
    End With
    With Range("K" & TextBox1.Value & ":L" & TextBox1.Value)
        .NumberFormat = "0.0"
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlTop
    End With
    Range("B" & TextBox1.Value & ":E" & TextBox1.Value).CheckSpelling
    MsgBox ("P.O.# " & TextBox8.Value & " added to Job# " & TextBox2.Value & " on Row# " & TextBox1.Value)
    For x = 1 To 8
        Me.Controls("TextBox" & x).Value = ""
    Next x
    For x = 10 To 22
        Me.Controls("TextBox" & x).Value = ""
    Next x
    TextBox1.SetFocus
    Application.ScreenUpdating = True
End Sub

 

[MrACED]

That worked perfectly!

Let me know if you have intrest or not in assisting with the following which still relates to this thread of placing data based on a user selected Row# in this case copying and pasting functionality

As far as another button that relates to this thread of picking where to place data by row#... sometimes we receive orders for the same as an old job or very similar so there is copying an pasting. In order to insert a little more data control... I was thinking of having 2 Copy options: Copy by Row# & Copy by PO#

Copy by Row#
+ This allows a user to select a Row# To Copy.
+ Once the user enters a Row# is it possible to autofill the user form with the information from that row?
+ Once the user form auto-fills the user would then pick a Row# to paste the copy to.
+ However there is some important items that I would like to control, before allowing the user to click the "Copy PO" button. For example the P.O.# Must be changed, it will never be the same as a previous PO. So maybe that text box can appear Red as disabled and then change to green once the user enters the new P.O.# which will then allow them to finish by clicking the "Copy PO" button

Copy by PO#
This does the same thing except allows the user some better functionality if they have an old po# in front of them versus scrolling down the list to find a row# to copy... In both scenarios they would still enter a Row# for where to paste the copied data like we have been doing and still relates to this thread of placing data (in this case copying) to a selected row# on the sheet.

Attached is drop box link to our MrAced(4) Spreadsheet, of which I have added 2 buttons for copying... and started 2 new user forms (1 for each button)
I imagined two separate buttons for two separate user forms... but maybe this can be done with one-button one-user form that allows the user to enter EITHER a row# or PO# and then auto-fills according to what they enter? You can advise accordingly.

MrACED (4).xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

I'll give this a "go" and get back to you.

 

[mumps]

Click here for your file. I have made some changes to the user forms. It is important to follow the instructions on any messages that pop up.

 

[MrACED]

Functionally it works great similar to how I thought it would!

However the user form needs to be as efficient as possible, Is there a way to code it that allows the user to be able to type in a PO# vs scrolling through a combo box? Kind of how Vlookup works in the worksheet? It's just not efficient as we have thousands of POs on our list... Imagine having to scroll through 5000+ lines of P.O. #s to find the one your looking for vs typing a known # you have in front of you.

 

[mumps]

Click here to download the file.