Auto list names

[globalStar]

hi all:

This is my first post in this forum, and i hope i could find my
request here .

(see the attached file for detailes )

[Charlize]

Add this to your userform coding.[vba]Public vdoublecheck As Boolean
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim extrainfo As String
vdoublecheck = False
extrainfo = "Already present ..."
If Application.WorksheetFunction.CountIf(Range("B2:B" & _
Range("B" & Rows.Count).End(xlUp).Row), Me.TextBox1.Text) >= 1 Then
For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
extrainfo = extrainfo & vbLf & cell.Value
Next cell
MsgBox extrainfo, vbExclamation
vdoublecheck = True
End If
End Sub
Private Sub TextBox2_Enter()
If vdoublecheck = True Then
vdoublecheck = False
Me.TextBox1.SetFocus
End If
End Sub[/vba]

[globalStar]

Thank you very much Charlize
Your post is good, but if you noticed the line:
[vba]extrainfo = extrainfo & vbLf & cell.Value[/vba]
it will be littel annoying if the Salesmen list is long !

If we confined with a message tell that name is already presnt !

And what about the the first requst ?

Sorry, I'm very greedy.

[Charlize]

1st request (because you asked it so nicely). Don't forget to remove the entries in column B on Total sheet.[VBA]Sub Add_Sales_Move_To_Total()
'A collection can't hold duplicates
Dim myColl As New Collection
'Salesperson on Sales Movement Sheet
Dim vItem
'Rowno of lastrow on Total sheet
Dim vNo
Dim wsStart As Worksheet
Dim wsTarget As Worksheet
Set wsStart = Worksheets("Sales Movement")
Set wsTarget = Worksheets("Total")
With wsStart
'On error when adding to collection
'move on with the code
On Error Resume Next
For Each vItem In .Range("A2:A" & _
.Range("A" & Rows.Count).End(xlUp).Row)
myColl.Add vItem.Text, CStr(vItem.Text)
Next vItem
End With
'Reset error trapping to normal behaviour
On Error GoTo 0
'Write collection values to sheet Total
With wsTarget
vNo = .Range("B" & Rows.Count).End(xlUp).Row
For Each vItem In myColl
vNo = vNo + 1
.Range("B" & vNo).Value = vItem
Next vItem
End With
End Sub[/VBA]

[lucas]

Globalstar,
Charlize has given you most of what you need in the posts above...maybe you can work some of it out for yourself instead of asking others to do all of your work for you....you are supposed to be learning.

Try to work it out and if you have specific questions...post them here.

[globalStar]

Thanks indeed Charlize ....

you helped me...

[Charlize]

A suggestion for your form. Instead of a Textbox, use a combobox. The combobox list can hold the names of the salespersons that are still available.

1. On the form initialize event we do an already chosen check.
2. If person on person list occures at the salessheet, we don't add that person to the list of the combobox

This approach has the advantage that the list is only holding the names that are still available.

Perhaps you like this way better than the messagebox way.