inserting new row

[viper]

If I am in cell A45 and I want a macro to insert a new row based on a user response how would I write that?

I need for the macro to be able to insert a new row based on what ever the users current position is if needed. If in cell A45 and need new row then insert new row, if in cell A65 and need new row then insert new row.

I am trying to make a sheet with a userform that the user will input a code number(1-85) and then enter the data from the invoice, but they want to be able to have multiple items under the same code so I thought if after the intial data is transferred I'd ask if they have another item for the same code, if so I'd bring back the userform and after they entered the second items data it would insert a new row under the previous item(sub category)and then put that data into the new line.

But I can't figure out how to write it:

Thanks,

[Tom Schreiner]

Hey Viper.
I'm not real clear on what you need.
This will insert a row below the "Active" cell.

Cells(ActiveCell.Row + 1, 1).Rows.Insert

Tom

[Tom Urtis]

Since you need user interaction, an input box might do the job. Also, you can give the users the option to enter as many rows as they want, with a macro like this:

Sub InsertRow()
Dim Rquan
Rquan = InputBox("Enter the number of rows required, based on" & vbCrLf & _
"how many items you have for this code number:", "How many rows do you need?")
If Rquan = "" Then Exit Sub
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rquan - 1, 0)).EntireRow.Insert
End Sub

Any help?

_________________
Tom Urtis

[ This Message was edited by: Tom Urtis on 2002-05-12 11:10 ]

[viper]

Thanks for your suggestions. But I think I'm doing this the hard way. Here is my code(Thanks again Tom):
If TextBox1.Value > 0 And TextBox1.Value < 86 Then
ActiveSheet.Cells(TextBox1.Value +7,4).Value = TextBox4.Text
How would I change/add to this so that it will insert a row if the cell above is not empty. Right now, with the code above everytime I make a new entry it will overwrite the previous entry, I would like to just have it insert a new row so that I can see all costs under the same code.

[Tom Schreiner]

Hi Viper...
I think I recognize that code from another thread. If we're going to be inserting rows, then you might need another approach. Could use find, a for..each loop, ect...
Post the code from your userform and maybe summarize what you are needing to do.
Thanks,
Tom

[viper]

This is what I'm trying to do.

item code description cost
01 nails 25.00
02 lumber 500.00

now if they have another item for item code 01 I need for a macro to insert a row under the top one so that it looks like this.

item code description cost
01 nails 25.00
hammer 15.00
02 lumber 500.00

here's the code:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("password")
If Not IsNumeric(TextBox1) Then
MsgBox "Need a Number"
TextBox1.SetFocus
Exit Sub
End If
'insert user data from cost box to sheet
If TextBox1.Value > 0 And TextBox1.Value < 86 Then

ActiveSheet.Cells(TextBox1.Value + 7, 4).Value = _
TextBox4.Text
Else
MsgBox "Range 1 to 85"
TextBox1.SetFocus
Exit Sub
End If
ActiveSheet.Protect ("password")
'copy invoice number and invoice total to next sheet
Sheets("Invoice listing").Activate
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
ActiveSheet.Unprotect ("password")
Cells(NextRow, 1) = TextBox5.Value
Cells(NextRow, 2) = TextBox4.Value
ActiveSheet.Protect ("password")
'check for other entries
Sheets("Sheet1").Activate
Msg = ("Enter another invoice?")
Config = vbYesNo + vbQuestion
Ans = MsgBox(Msg, Config)
If Ans = vbYes Then
TextBox1.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
End If
If Ans = vbNo Then
Unload UserForm1
End If
End Sub

And yes I think I will need sometype of loop until the user clicks no.
But it will only need to insert a new row when one or more entries are made in the same item code.

So if item code 01 has three items each will need to listed separately.

item code description cost
01 nails 25.00
insert new row for next item
hammer 15.00
insert new row if another item
screws 10.00

_________________
Soon I hope to be answering alot of these questions instead of asking them.

viper

[ This Message was edited by: viper on 2002-05-12 20:44 ]

[Tom Schreiner]

Hi Viper...
I'm having a difficult time following you here.
Questions:
Are you listing a database of sorts containing a complete list of available items on the active sheet in a column in rows 1 to 85?
The user selects an item from a userform and an invoice is built on a seperate sheet?
Sorry to pester you for deatails, but I don't have a good enough grasp on your structure to really offer you any help.
Post LOTS of details.
Thanks,
Tom