dessa
Fri May 09 03:28:01 CDT 2008
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" <pclive(RemoveThis)@cox.net> wrote in message
> news:%23vC$%239RsIHA.2188@TK2MSFTNGP04.phx.gbl...
> > 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" <dessa@discussions.microsoft.com> wrote in message
> > news:352FB4E9-3F4B-4037-BDAD-430B37A02896@microsoft.com...
> >> 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" <dessa@discussions.microsoft.com> wrote in message
> >>> news:C6354882-5D97-46F9-A3AE-6CC0471FB837@microsoft.com...
> >>> > 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
> >>>
> >>>
> >>>
> >
> >
>
>
>