PDA

View Full Version : Solved: Script to add rows with userform data



xfr79
12-16-2008, 01:27 PM
I'm in need of a script that asks a user for a name to input.
After a user enters the name, it would insert a row in the current worksheet and put the name in the next available row in column A. It would then go through each worksheet to the right of the current worksheet the user is on and insert the name in the next available row.

If the user wanted to delete a name, they would select the cell the name is in and delete the row. It would then also check each sheet to the right and delete any row that also contains that name.

Excel capable of doing this?

Thank You.

xfr79
12-16-2008, 02:49 PM
I found a script that adds inputbox data into a row.

A couple of things this doesn't do.
It does not find the next available row to insert inputbox data.
It also does not add the inputbox data to the worksheets to the right of the current activeworksheet.

can these be accomplished?


Dim NewRow As Integer
NewRow = Worksheets("Oct08").Range("A1").Value + 1
If Len(MyForm.fName.Value) = 0 Then
MsgBox "The name field can not be left empty!", vbOKOnly, "MeadInKent"
MyForm.fName.SetFocus
Exit Sub
End If
Worksheets("Oct08").Cells(NewRow, 1).Value = MyForm.fName.Value
MyForm.Hide

Benzadeus
12-16-2008, 03:17 PM
To insert a name:


Sub InsertName()

inpData = InputBox("Name to insert?")

For s = ActiveSheet.Index To Sheets.Count
Sheets(s).Cells(Sheets(s).Range("A65536").End(xlUp).Row + 1, 1) = inpData
Next s

End Sub

Benzadeus
12-16-2008, 03:25 PM
To remove a name:

Sub RemoveName()

inpData = Application.InputBox("Select name to delete in this worksheet and others at right")
For s = ActiveSheet.Index To Sheets.Count
Set c = Sheets(s).Range("A1:A65536").Find(inpData)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Next s
End Sub

xfr79
12-17-2008, 06:58 AM
Wonderful Benzadeus, that worked perfectly!!

If all possible, can the script also sort in alphabetical order?
I would like for it to do that, but instead of sorting the cell, I want it to sort the rows in alphabetical order according to the name in column A.

So if the first couple names are:


A B C D
1 John Smith 5.00 5.00 5.00
2 Lucy Jones 7.00 7.00 7.00


and I added a name with the script you provided:


A B C D
1 John Smith 5.00 5.00 5.00
2 Lucy Jones 7.00 7.00 7.00
3 Andy Hill 0.00 0.00 0.00




After the name is added it sorts it out by rows in alphabetical order according to the name in Column A:


A B C D
1 Andy Hill 0.00 0.00 0.00
2 John Smith 5.00 5.00 5.00
3 Lucy Jones 7.00 7.00 7.00



Possible?

ps.
sorry about code tags, that was the only way to format it right. :)

xfr79
12-17-2008, 07:18 AM
Nevermind, I got it, I didn't realize Data:Sort did rows if selected. :)

Benzadeus
12-17-2008, 07:20 AM
In that case, please write "solved" before title at topic.

xfr79
12-17-2008, 08:06 AM
Actually I got one more request.
This Script:

Sub Addname()

inpData = InputBox("Name to insert?")

For s = ActiveSheet.Index To Sheets.Count
Sheets(s).Cells(Sheets(s).Range("A10").End(xlUp).Row + 1, 1) = inpData
Next s

End Sub

If the script reaches Range A10, it would add a row above A10 and continue on with the script.

Benzadeus
12-17-2008, 09:28 AM
So the first 9 data entered will be fixed and the new entries will be inserted at row 10, pushing the old records down?

xfr79
12-17-2008, 09:37 AM
The data from the script actually starts at Line A3.
A10 is a total line from numbers that is entered in other cells.
I actually just realized that A10 is going to be moved down if more cells are inserted above. A10 needs to be the very last row.
So I guess what I'm actually asking now, line A10 contains text "TOTAL AVERAGE", if the script comes across "TOTAL AVERAGE" it will insert a row above it.

xfr79
12-17-2008, 09:37 AM
EDIT:
Sorry, double post the reply.

Benzadeus
12-17-2008, 10:08 AM
Ok, assuming that this action will affect all sheets:


Sub InsertName()

inpData = InputBox("Name to insert?")

For s = ActiveSheet.Index To Sheets.Count
If [A65536].End(xlUp).Row >= 10 Then
Rows(11).Insert
Sheets(s).[A11] = inpData
Else
Sheets(s).Cells(Sheets(s).[A65536].End(xlUp).Row + 1, 1) = inpData
End If
Next s

End Sub

xfr79
12-17-2008, 10:20 AM
Perfect!,
Thank You Very Much!!
:beerchug: