Insert Row based on User Selected Row#

MrACED

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

MrACED

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
 

MrACED

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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?

Format Tweak.png
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,628

ADVERTISEMENT

Try:
VBA Code:
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

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

User Form2_Copy PO Based on Row #_Copy to a specific row#.png


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.

 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628

ADVERTISEMENT

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

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,628
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

New Member
Joined
Nov 25, 2020
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,677
Messages
5,720,259
Members
422,273
Latest member
linds75

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
Top