VBA Insert Row At Specific Location as Determined By a Value In a Cell

[MBolt]

Hello,

I am new to the forum, thank you for having me. I am trying to learn some basic VBA for data entry purposes at work. My goal is to create an easy to use userform as a type of record database that can add new, insert, and delete records. I have nearly the entire thing functional, but I have finally hit a wall.

The userform allows a user to enter a new operation number, and then select whether or not the operation occurs first, after a specific operation, or at the same time as a specific operation. I have the "first" one figured out and it inserts a new row via

If Me.is_first_button.Value = True Then Range("2:2").Insert CopyOrigin:=xlFormatFromRightOrBelow

The first row contains command buttons, so this basically inserts a new row at the top and an additional line in the code transfers data from the userform to the appropriate row/columns that are created.

Now, when the user selects either the "After" or "At Same Time As" combo boxes, it unlocks a text box that expects the user to key in an operation value. I cant figure out how to get the value from this text box INTO the range portion of the range.insert script, so that it knows where to insert the row.

For example:

User is building "Operation 11" and the operation number is keyed as 11. The user then selects the AFTER option box indicating this operation occurs AFTER an operation, then they type in 23. When the user submits the form via command button, a new row is added below row 23, and the script knows to insert the data from the worksheet into this row.

Any thoughts? Even getting it to insert a blank row correctly would help, im sure i can figure out how to insert it.

thanks in advance.

 

[JLGWhiz]

When you say the user keys in 23, it is assumed that it wil be keyed into the text box that was unlocked. Call it TextBox1 for illustartion purposes

Dim r As Range
Set r = Rows(Me.TextBox1.Value + 1)
r.Insert
r.Cells(1) = 'Something Or 
Cells(r.Row, 1) = 'Something - these statements are equivalent but only use one or the other.
r.Cells(2) = 'Something Or 
Cells(r.Row, 2) = 'Something