Insert new name to sorted list

Resolved · Medium Priority · Version 2003

Mon 10 Oct 2011, 14:33 Delegate David said...

David has attended:
Excel VBA Intro Intermediate course

Insert new name to sorted list

Hi,

I have a column with employee names with info relating to each employee running along the colums next to each name.

I would like to make it easy for users to insert new employees into the list so that a new employee name enetered into an input box will be inserted at the correct point (alphabetically) on the list of names.

Formulas would also need to be copied down into the newly inserted row

Is this possible?

Thanks,

David

Tue 11 Oct 2011, 12:26 Trainer Mark said...

RE: Insert new name to sorted list

Hello David,

What you are asking is possible, however, it would take a fair bit of coding. First you would be better creating a userform that has the fields of info required from users, e.g. name, tel, address etc.., these would be stored in variables.
When the user clicks a command button on the form, you could get the code to place the info at the end of the list of current records. Then have the code copy relevant column formulas down, and finally sort the name column.

If you need any help with the code please let me know. but if you take each step seperately and code, the whole process is made easier.

Using the Inputbox method would require the box to appear numerous times!

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Mark
Microsoft Office Specialist Trainer

Wed 12 Oct 2011, 16:06 Delegate David said...

RE: Insert new name to sorted list

Hi Mark,

Good idea.
Should be able to do this. I'll let you know if I get stuck at all.

Lukily I've only got two bits of info needed so should only need two input boxes.

Best regards,

David

Fri 14 Oct 2011, 10:42 Trainer Doug said...

RE: Insert new name to sorted list

Hi David

As Mark suggested an Input form would be best idea.
But if only one or two columns you could take a look at the code below and attached simple example sheet. Cheers, Doug

Sub AddName()
Dim strName As String
Dim strsheet As String

' AddName Macro in Excel 2003
' Asks to input a person's name
' convert to proper case
' Inserts a new row
' Creates a Sum function in the total column
' sorts table by Name

strName = InputBox("Enter Full name", "Please type in full")

If strName = "" Then Exit Sub

strName = Application.WorksheetFunction.Proper(strName)
strsheet = ActiveSheet.Name

Sheets(strsheet).Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Value = strName
ActiveCell.Offset(0, 4).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

Range("A1").Select
Range(Cells(2, 1), Cells(Range("A1").CurrentRegion.Rows.Count, Range("A1").CurrentRegion.Columns.Count)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub