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
client. When they hit OK, it adds the client to the list in alphabetical
order where the validation is pulled from (no more than 500 clients). On top
of that, when they hit OK on the Message Box, Excel will actually create a
new tab at the bottom, cahnge the name of that tab to the client's name, and
put it in alphabetical order with the other tabs aready in existance.

Any help would be appreciated. I am using Excel 2003 on Windows Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in alphabetical
order where the validation is pulled from (no more than 500 clients). On
top
of that, when they hit OK on the Message Box, Excel will actually create a
new tab at the bottom, cahnge the name of that tab to the client's name,
and
put it in alphabetical order with the other tabs aready in existance.

Any help would be appreciated. I am using Excel 2003 on Windows Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in alphabetical
order where the validation is pulled from (no more than 500 clients). On
top
of that, when they hit OK on the Message Box, Excel will actually create a
new tab at the bottom, cahnge the name of that tab to the client's name,
and
put it in alphabetical order with the other tabs aready in existance.

Any help would be appreciated. I am using Excel 2003 on Windows Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in
alphabetical
order where the validation is pulled from (no more than 500 clients).
On
top
of that, when they hit OK on the Message Box, Excel will actually
create a
new tab at the bottom, cahnge the name of that tab to the client's
name,
and
put it in alphabetical order with the other tabs aready in existance.

Any help would be appreciated. I am using Excel 2003 on Windows Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in
alphabetical
order where the validation is pulled from (no more than 500 clients).
On
top
of that, when they hit OK on the Message Box, Excel will actually
create a
new tab at the bottom, cahnge the name of that tab to the client's
name,
and
put it in alphabetical order with the other tabs aready in existance.

Any help would be appreciated. I am using Excel 2003 on Windows Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in
alphabetical
order where the validation is pulled from (no more than 500
clients).
On
top
of that, when they hit OK on the Message Box, Excel will actually
create a
new tab at the bottom, cahnge the name of that tab to the client's
name,
and
put it in alphabetical order with the other tabs aready in
existance.

Any help would be appreciated. I am using Excel 2003 on Windows
Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in
alphabetical
order where the validation is pulled from (no more than 500
clients).
On
top
of that, when they hit OK on the Message Box, Excel will actually
create a
new tab at the bottom, cahnge the name of that tab to the client's
name,
and
put it in alphabetical order with the other tabs aready in
existance.

Any help would be appreciated. I am using Excel 2003 on Windows
Server
2003. Thanks!

Aaron

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
client. When they hit OK, it adds the client to the list in
alphabetical
order where the validation is pulled from (no more than 500
clients).
On
top
of that, when they hit OK on the Message Box, Excel will actually
create a
new tab at the bottom, cahnge the name of that tab to the
client's
name,
and
put it in alphabetical order with the other tabs aready in
existance.

Any help would be appreciated. I am using Excel 2003 on Windows
Server
2003. Thanks!

Aaron

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

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

I Need to find a macro, that will find a number in a column with Pop up box
for number serching for.

Thank you very much if you can help

Donald E