naming worksheets
 

I want to match the name of a worksheet tab with the header of a form.
In other words, if I type the name of the form in a header (a
cell), the worksheet tab would change the name to match the header
name.

Here's what I'm using but it's not working:

Private Sub workbook_SheetChange(ByVal sh As Object, ByVal target As
Range)
If target.Address = "$d$1" Then sh.Name = target
End Sub

Can anyone tell me what I'm doing wrong? THANKS!


--
jerrystan
------------------------------------------------------------------------
jerrystan's Profile: http://www.excelforum.com/member.php...o&userid=28573
View this thread: http://www.excelforum.com/showthread...hreadid=494190

naming worksheets
 

Has to be upper-case

Private Sub workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
If Target.Address = "$D$1" Then sh.Name = Target.Value
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jerrystan" wrote
in message ...

I want to match the name of a worksheet tab with the header of a form.
In other words, if I type the name of the form in a header (a
cell), the worksheet tab would change the name to match the header
name.

Here's what I'm using but it's not working:

Private Sub workbook_SheetChange(ByVal sh As Object, ByVal target As
Range)
If target.Address = "$d$1" Then sh.Name = target
End Sub

Can anyone tell me what I'm doing wrong? THANKS!


--
jerrystan
------------------------------------------------------------------------
jerrystan's Profile:
http://www.excelforum.com/member.php...o&userid=28573
View this thread: http://www.excelforum.com/showthread...hreadid=494190

naming worksheets
 

Try:

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

Regards,
Greg

"jerrystan" wrote:


I want to match the name of a worksheet tab with the header of a form.
In other words, if I type the name of the form in a header (a
cell), the worksheet tab would change the name to match the header
name.

Here's what I'm using but it's not working:

Private Sub workbook_SheetChange(ByVal sh As Object, ByVal target As
Range)
If target.Address = "$d$1" Then sh.Name = target
End Sub

Can anyone tell me what I'm doing wrong? THANKS!


--
jerrystan
------------------------------------------------------------------------
jerrystan's Profile: http://www.excelforum.com/member.php...o&userid=28573
View this thread: http://www.excelforum.com/showthread...hreadid=494190

naming worksheets
 

Thanks guys, but I'm still getting an error. Could my other macros in
this file be affecting it??


--
jerrystan
------------------------------------------------------------------------
jerrystan's Profile: http://www.excelforum.com/member.php...o&userid=28573
View this thread: http://www.excelforum.com/showthread...hreadid=494190

naming worksheets
 

Did you put the code in the ThisWorkbook code module?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jerrystan" wrote
in message ...

Thanks guys, but I'm still getting an error. Could my other macros in
this file be affecting it??


--
jerrystan
------------------------------------------------------------------------
jerrystan's Profile:
http://www.excelforum.com/member.php...o&userid=28573
View this thread: http://www.excelforum.com/showthread...hreadid=494190

naming worksheets
 

Your code will error out if there is already a sheet by that name or if you
try to use name the sheet "History". You can add this procedure to check if a
sheet by that name already exists...

Public Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

Additionally the code will not catch if anything is pasted into the cell.
The only code that should affect this is application.enableevents = ?? which
could effectively turn it off, but it will not cause an error.
--
HTH...

Jim Thomlinson


"jerrystan" wrote:


Thanks guys, but I'm still getting an error. Could my other macros in
this file be affecting it??


--
jerrystan
------------------------------------------------------------------------
jerrystan's Profile: http://www.excelforum.com/member.php...o&userid=28573
View this thread: http://www.excelforum.com/showthread...hreadid=494190

naming worksheets
 

I set up another workbook to test this and it works in there. It mus
have something to do with my existing code. I checked for duplicat
names and the "history" name and its all good. I'll keep messing wit
it. Thanks for your help

--
jerrysta
-----------------------------------------------------------------------
jerrystan's Profile: http://www.excelforum.com/member.php...fo&userid=2857
View this thread: http://www.excelforum.com/showthread.php?threadid=49419