Aaron
It just occurred to me that you might not have setup the Data Validation
cell correctly if you didn't know about naming a range. The setup requires
the name of that range.
Select the Data Validation cell. Click on Data - Validation. In the
"Allow:" space, select "List". In the "Source:" space, type
"=ListOfClients" without the quotes. Click OK.
This ensures that the list displayed in the Data Validation cell will
always be the updated list. Otto
"KnightRiderAW" wrote in message
...
That's it! Thanks!

"Otto Moehrbach" wrote:

Aaron
No problem. In Excel you can apply a range name to a range, be it
one
cell or many cells. Yes, the list of clients resides in the Utility
sheet,
starting in A1 and going down.
What you need to do is name that range. This is how. Select all the
cells in the list of clients. Now click on Insert - Name - Define. A
dialog box pops up. In the space labeled "Names in workbook:", type
"ListOfClients" without the quotes. Click OK. That's it. Now, run the
code and see what happens. Post back if you need more help. HTH Otto
"KnightRiderAW" wrote in
message
...
Thanks, Otto. I got the word wrap issue resolved initially. That is
not
the
problem. I guess I am a littel confused on what you are calling the
"ListOfClients." I thought that the list was to reside under the
"Utility"
Tab and start at A1 and work down (as I have it set up). Where does
the
"ListOfClients" list actually come in? I must be missing something
somewhere. You said name the range "ListOfClients." I'm not sure
where
or
what you are talking about there. Thanks for helping a confused
individual
with this!

Aaron

"Otto Moehrbach" wrote:

Aaron
A number of different things could be the cause of the error.
Let's
take them one at a time:
First thing:
Line wrapping is a problem with these newsgroup postings. If a line
of
code
is written on one line (as I did with the suspect line of code), and
you
have it on 2 lines, Excel will not accept it. The line you cited
starts
with "If Not Range" and ends with "Is Nothing Then" without the
quotes.
This must all be on one line. Is it?
Next thing:
If you don't have a range somewhere in your file that is named
"ListOfClients" BEFORE THE CODE RUNS, you will get an error with that
line
of code. Note that the name, "ListOfClients", is a range name in the
spreadsheet, not a variable in the code. From what you say, you might
not
have named your list of clients "ListOfClients". The code requires
that
the
client list be named "ListOfClients" before the code is triggered to
run.
The code resets the list when the user types in a name that is not in
the
list, and then names the reset list "ListOfClients", yes, but the list
must
be named by you before the code runs to get the ball rolling.
Check these things and let me know what you find. Otto

"KnightRiderAW" wrote in
message
...
Otto,

Thanks for this. I think this is exactly what I need looking
through
the
code. However, the Sub SetUpClient is returning an error and I
can't
solve
it. I have the sheet named "Utility" and I have placed the firing
macro
in
the first sheet module while the rest are in the standard module.
The
macro
does fire when text is entered into the cell (in my case, D6).
However,
the
line "If Not Range ("ListOfClients")... is returning an error
(Application-defined ot object-defined run-time error '1004). I was
a
little
confused on what to do with the "ListOfClients" statement as you
said
it
was
the actual list. I thought the actual list was in the Utility sheet
starting
in A1. Thanks for your help in resolving this.

Aaron

"Otto Moehrbach" wrote:

Aaron

I assumed the Data Validation cell is B2

I assumed that the list of clients is in a separate sheet named
"Utility",
in Column A starting in A1.

I assumed the client list is named "ListOfClients".

Change the above to suit what you have but note that "Utility" and
"ListOfClients" is written in the code and will have to be changed
in
the
code if you change them in your file.

The code I wrote for you consists of one sheet macro (belongs in
the
sheet
module for the sheet that holds the Data Validation cell) and 3
macros
that
go in a standard module.

The sheet macro in the sheet module fires whenever the content of
ANY
cell
in the sheet changes. The code I wrote in that macro restricts the
macro
from doing anything unless the content of B2 changes. The Data
Validation
must be setup to allow the user to type in anything he wants

If the content of cell B2 changes, the code will assign that entry
to
the
variable ClientName and will call the SetUpClient macro,

If ClientName is in the existing client list, the code will select
that
client's sheet and do nothing more.

If the ClientName is NOT in the client list, the code will do the
following:

Put that name in the client list.

Sort the client list.

Assign that new list to the B2 Data Validation cell.

Create a new blank sheet with the new client name as the sheet
name.

Sort the sheets alphabetically.

Select the new sheet.



Let me know how this works for you and any changes you might want.
Otto



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address(0, 0) < "B2" Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
ClientName = Target.Value
Call SetUpClient
End Sub



Option Explicit
Option Compare Text
Dim OnList As Boolean
Public ClientName As String

Sub SetUpClient()
Application.EnableEvents = False
Range("B2").ClearContents
Application.EnableEvents = True
If Not Range("ListOfClients").Find(What:=ClientName,
LookAt:=xlWhole)
Is
Nothing Then
Sheets(ClientName).Select
Else
Application.ScreenUpdating = False
Call PutNameInList
Sheets(ClientName).Select
Application.ScreenUpdating = True
End If
End Sub



Sub PutNameInList()
With Sheets("Utility")
.Range("A" & Rows.Count).End(xlUp).Offset(1).Value =
ClientName
.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Name =
"ListOfClients"
.Range("ListOfClients").Sort Key1:=.Range("A1"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End With
Worksheets.Add.Name = ClientName
Call SortSheetsAlpha
End Sub



Sub SortSheetsAlpha()
Dim Counter As Long
Dim Counter1 As Long
For Counter = 1 To Sheets.Count - 1
For Counter1 = Counter + 1 To Sheets.Count
If Sheets(Counter1).Name < Sheets(Counter).Name Then
Sheets(Counter1).Move Befo=Sheets(Counter)
Sheets(Counter + 1).Move Befo=Sheets(Counter1)
End If
Next Counter1
Next Counter
End Sub


"KnightRiderAW" wrote in
message
...
Really, all I want to happen is have the choice of clients. It
doesn't
have
to go their tab or anything automatically. It simply needs to be
able
to
let
them choose a client's name or if it is not already listed, add
it
to
the
list and create a new tab for that client. Eventually, all the
rest
of
the
information that they type in will go to the specific tab
automatically
through another macro I already have set up. I would prefer to
have
the
macro for this listed on the site in its code so that others can
benefit
from
this. Thanks for understanding and working on this.

Aaron

"Otto Moehrbach" wrote:

Aaron

How about no message box or Input Box? Also how about no
button?
Just
the Data Validation List. You can put instructions next to the
Data
Validation cell telling the user to select from the list or
type
in
the
name of a new client. The code will fire when he hits enter.
If
the
entry
is not on the list, the entry will be put in the list in
alphabetical
order
and a new sheet will be created with that client's name as the
sheet
name,
the sheets will be sorted, and the new client's sheet will be
selected.

You didn't say what you want to happen if the client
selected
is
on the list. I assume you want that client's sheet selected.

This will involve a series of macros (one big macro
is
cumbersome) including a sheet macro. I will develop it for you
and
send
you
the file. Attaching a file to a newsgroup post is a no-no, so I
will
email
it to you. Send me your email address. I'll probably have some
questions
to ask you as I develop the code too. My email address is
. Remove the "nop" from this address.
HTH
Otto

"KnightRiderAW" wrote
in
message
...
I have a worksheet that some of our staff uses to input their
hours
into.
Under a client section, they have a list box that allows them
to
select
the
client they are working for. However, I want them to be able
to
add
a
new
client if it is not already listed.

I have a button at the bottom that they can click. What I
wold
like
to
see
happen is have a Message Box pop up and ask them for the name
of
the
new