is it possible to force a cell enty as a sheet name, i.e. i have a worksheet
that i use for quoting for clients. i wrote a macro that automatically put a
new number on each sheet when it duplicate the master sheet. i would like
that quote number in the worksheet, to be used as the sheet number for easy
reference

ActiveSheet.name=Range("A1").value

Does that help?
Paul

--

"des-sa" wrote in message
...
is it possible to force a cell enty as a sheet name, i.e. i have a
worksheet
that i use for quoting for clients. i wrote a macro that automatically
put a
new number on each sheet when it duplicate the master sheet. i would like
that quote number in the worksheet, to be used as the sheet number for
easy
reference

Paul,
You're working with stupidity here. Where should i enter that formula?
Thanks

"PCLIVE" wrote:



ActiveSheet.name=Range("A1").value

Does that help?
Paul

--

"des-sa" wrote in message
...
is it possible to force a cell enty as a sheet name, i.e. i have a
worksheet
that i use for quoting for clients. i wrote a macro that automatically
put a
new number on each sheet when it duplicate the master sheet. i would like
that quote number in the worksheet, to be used as the sheet number for
easy
reference

This is VB (Visual Basic) code just like what a macro would use, not a
formula. Without the use of a UDF (User Defined Function) which is also
done through VB, then I don't know of a way to make the sheet name = the
contents of a specific cell upon enter a value in a cell, assuming that is
what you wanted to do.

Try this.

Press Alt+F11 to enter the VB Editor.
On the left, you should see your workbook listed as
"VBAProject(YourWorkbook)". If there is a plus to the left of it, click it
to make it a minus which expands the list as well. Do the same thing for
"Microsoft Excel Objects".
Double-click on ThisWorkbook.
Paste the following code in the right-hand pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then ActiveSheet.Name = Range("A1").Value
End Sub


This code assumes that you want the worksheet name to match the contents
that are in cell A1. If you want it to equal some other cell, then modify
as necessary. Keep in mind, this code will change the sheet name of the
active sheet to whatever is entered into A1. The code is triggered any time
the sheet is changed and the changed cell was A1. If you delete the
contents of A1, this code will result in error.

Let me know if this is what you were looking for.
Regards,
Paul

--

"des-sa" wrote in message
...
Paul,
You're working with stupidity here. Where should i enter that formula?
Thanks

"PCLIVE" wrote:



ActiveSheet.name=Range("A1").value

Does that help?
Paul

--

"des-sa" wrote in message
...
is it possible to force a cell enty as a sheet name, i.e. i have a
worksheet
that i use for quoting for clients. i wrote a macro that automatically
put a
new number on each sheet when it duplicate the master sheet. i would
like
that quote number in the worksheet, to be used as the sheet number for
easy
reference

My mistake. That's not a UDF. I started on one theory and ended with
another. Also, to prevent the error that I mentioned, used this modified
code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" And Range("A1").Value < "" Then ActiveSheet.Name
= Range("A1").Value
End Sub


HTH,
Paul

--

"PCLIVE" wrote in message
...
This is VB (Visual Basic) code just like what a macro would use, not a
formula. Without the use of a UDF (User Defined Function) which is also
done through VB, then I don't know of a way to make the sheet name = the
contents of a specific cell upon enter a value in a cell, assuming that is
what you wanted to do.

Try this.

Press Alt+F11 to enter the VB Editor.
On the left, you should see your workbook listed as
"VBAProject(YourWorkbook)". If there is a plus to the left of it, click
it to make it a minus which expands the list as well. Do the same thing
for "Microsoft Excel Objects".
Double-click on ThisWorkbook.
Paste the following code in the right-hand pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address = "$A$1" Then ActiveSheet.Name =
Range("A1").Value
End Sub


This code assumes that you want the worksheet name to match the contents
that are in cell A1. If you want it to equal some other cell, then modify
as necessary. Keep in mind, this code will change the sheet name of the
active sheet to whatever is entered into A1. The code is triggered any
time the sheet is changed and the changed cell was A1. If you delete the
contents of A1, this code will result in error.

Let me know if this is what you were looking for.
Regards,
Paul

--

"des-sa" wrote in message
...
Paul,
You're working with stupidity here. Where should i enter that formula?
Thanks

"PCLIVE" wrote:



ActiveSheet.name=Range("A1").value

Does that help?
Paul

--

"des-sa" wrote in message
...
is it possible to force a cell enty as a sheet name, i.e. i have a
worksheet
that i use for quoting for clients. i wrote a macro that
automatically
put a
new number on each sheet when it duplicate the master sheet. i would
like
that quote number in the worksheet, to be used as the sheet number for
easy
reference

PAUL,
CANT THANK YOU ENOUGH!!!!!!!!!!!!
GOT IT RIGHT.
DISRI

"PCLIVE" wrote:

My mistake. That's not a UDF. I started on one theory and ended with
another. Also, to prevent the error that I mentioned, used this modified
code.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" And Range("A1").Value < "" Then ActiveSheet.Name
= Range("A1").Value
End Sub


HTH,
Paul

--

"PCLIVE" wrote in message
...
This is VB (Visual Basic) code just like what a macro would use, not a
formula. Without the use of a UDF (User Defined Function) which is also
done through VB, then I don't know of a way to make the sheet name = the
contents of a specific cell upon enter a value in a cell, assuming that is
what you wanted to do.

Try this.

Press Alt+F11 to enter the VB Editor.
On the left, you should see your workbook listed as
"VBAProject(YourWorkbook)". If there is a plus to the left of it, click
it to make it a minus which expands the list as well. Do the same thing
for "Microsoft Excel Objects".
Double-click on ThisWorkbook.
Paste the following code in the right-hand pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address = "$A$1" Then ActiveSheet.Name =
Range("A1").Value
End Sub


This code assumes that you want the worksheet name to match the contents
that are in cell A1. If you want it to equal some other cell, then modify
as necessary. Keep in mind, this code will change the sheet name of the
active sheet to whatever is entered into A1. The code is triggered any
time the sheet is changed and the changed cell was A1. If you delete the
contents of A1, this code will result in error.

Let me know if this is what you were looking for.
Regards,
Paul

--

"des-sa" wrote in message
...
Paul,
You're working with stupidity here. Where should i enter that formula?
Thanks

"PCLIVE" wrote:



ActiveSheet.name=Range("A1").value

Does that help?
Paul

--

"des-sa" wrote in message
...
is it possible to force a cell enty as a sheet name, i.e. i have a
worksheet
that i use for quoting for clients. i wrote a macro that
automatically
put a
new number on each sheet when it duplicate the master sheet. i would
like
that quote number in the worksheet, to be used as the sheet number for
easy
reference