Hi! Is there an event or a way to determine if a sheet's name has bee
changed by the user? :confused

--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=39968

I suspect you are concerned with the user changing the sheet tab name that
maybe used in your code? I always use the codename for the worksheet, that
way the user can change tabs and it does not affect the code. You can only
set the codename in the VBA editor or through VBA code itself.

If this is not the problem, then the only way I can think of, is to store
the sheet name on each sheet (perhpas a hidden location) and test the
current name against that ?

--
Cheers
Nigel



"T-®ex" wrote in message
...

Hi! Is there an event or a way to determine if a sheet's name has been
changed by the user?


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=399687

Yes, you're right... I am concerned w/ the user changing the sheet ta
name.
I know I can refer to the sheets using their codenames in VBA
Unfortunately, that won't work in my situation as users may add/delet
sheet(s).

In the prog I'm making, there are "special" sheets that I have to kee
track of. The names of these sheets are stored in a hidde
(xlVeryHidden) sheet. The prob is when users change the tab names, th
original tab names stored in the hidden sheet would now NOT coincid
with the new names.

My first approach to remedy this was to protect the workbook and onl
allow the users to change the tab names using a custom form and code
The problem is, other actions that are normally allowed in a
unprotected workbook are now disabled, e.g. rearranging sheets, etc..
My code is getting more and more complex and I think protecting th
workbook is not a very good idea... :(

Nigel Wrote:
I suspect you are concerned with the user changing the sheet tab nam
that
maybe used in your code? I always use the codename for the worksheet
that
way the user can change tabs and it does not affect the code. You ca
only
set the codename in the VBA editor or through VBA code itself.

If this is not the problem, then the only way I can think of, is t
store
the sheet name on each sheet (perhpas a hidden location) and test the
current name against that ?

--
Cheers
Nigel



"T-®ex" wrote i
message
...

Hi! Is there an event or a way to determine if a sheet's name ha
been
changed by the user?


--
T-®ex

------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread
http://www.excelforum.com/showthread...hreadid=399687


--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=39968

Hi,
I am not sure of the logic here?

You can still use codenames in all situations, if the very hidden sheet
contains a list of the special sheets, presumably these already exist, in
which case codenames will work. If the user adds a new sheet, use the
Workbook_NewSheet event to trigger the code to store/change the codename in
your tracking sheet.

In all cases you have a controlled set of codenames for all sheets.

Now if the user deletes a sheet, presumably this is acceptable, you
application will need to track the codename has having being deleted.

Cheers
Nigel



"T-®ex" wrote in message
...

Yes, you're right... I am concerned w/ the user changing the sheet tab
name.
I know I can refer to the sheets using their codenames in VBA.
Unfortunately, that won't work in my situation as users may add/delete
sheet(s).

In the prog I'm making, there are "special" sheets that I have to keep
track of. The names of these sheets are stored in a hidden
(xlVeryHidden) sheet. The prob is when users change the tab names, the
original tab names stored in the hidden sheet would now NOT coincide
with the new names.

My first approach to remedy this was to protect the workbook and only
allow the users to change the tab names using a custom form and code.
The problem is, other actions that are normally allowed in an
unprotected workbook are now disabled, e.g. rearranging sheets, etc...
My code is getting more and more complex and I think protecting the
workbook is not a very good idea... :(

Nigel Wrote:
I suspect you are concerned with the user changing the sheet tab name
that
maybe used in your code? I always use the codename for the worksheet,
that
way the user can change tabs and it does not affect the code. You can
only
set the codename in the VBA editor or through VBA code itself.

If this is not the problem, then the only way I can think of, is to
store
the sheet name on each sheet (perhpas a hidden location) and test the
current name against that ?

--
Cheers
Nigel



"T-®ex" wrote in
message
...

Hi! Is there an event or a way to determine if a sheet's name has
been
changed by the user?


--
T-®ex

------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399687



--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=399687

Here's the situation... There are 2 types of sheets in my prog
"special" sheets and "ordinary" sheets. The special sheets are sheet
that have been pre-formatted and are to be data validated. The use
creates these through a custom menu. Everytime a special sheet i
created, it is "registered" in the hidden sheet. What are stored in th
hidden sheet are the sheet's name, its formatting/settings, etc

The user has an option to change a special sheet's settings through
user form. (The changes in the settings are stored to the hidde
sheet.)

When data in a special sheet changes, vba codes are run to check th
validity of its data. I use a code something like:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If IsSpecial(Sh.Name) Then
'retrieve settings for the special sheet...
'additional code here...
End If
End Sub

The function IsSpecial determines whether the sheet in question i
special. It does this by searching for the name of the sheet in th
hidden sheet. (If the name is in the hidden sheet then the sheet i
special.)

If IsSpecial returns True, the settings for that special sheet are the
retrieved (from the hidden sheet). These values (settings) are the
used/compared against the changed range in the special sheet


Again, my problem is when the user changes a special sheet's tab name
how do I update the affected row in the hidden sheet to reflect the ne
name? :(


Nigel Wrote:
Hi,
I am not sure of the logic here?

You can still use codenames in all situations, if the very hidde
sheet
contains a list of the special sheets, presumably these already exist
in
which case codenames will work. If the user adds a new sheet, use the
Workbook_NewSheet event to trigger the code to store/change th
codename in
your tracking sheet.

In all cases you have a controlled set of codenames for all sheets.

Now if the user deletes a sheet, presumably this is acceptable, you
application will need to track the codename has having being deleted.

Cheers
Nigel



"T-®ex" wrote i
message
...

Yes, you're right... I am concerned w/ the user changing the shee
tab
name.
I know I can refer to the sheets using their codenames in VBA.
Unfortunately, that won't work in my situation as users ma
add/delete
sheet(s).

In the prog I'm making, there are "special" sheets that I have t
keep
track of. The names of these sheets are stored in a hidden
(xlVeryHidden) sheet. The prob is when users change the tab names
the
original tab names stored in the hidden sheet would now NOT coincide
with the new names.

My first approach to remedy this was to protect the workbook an
only
allow the users to change the tab names using a custom form an
code.
The problem is, other actions that are normally allowed in an
unprotected workbook are now disabled, e.g. rearranging sheets
etc...
My code is getting more and more complex and I think protecting the
workbook is not a very good idea... :(

Nigel Wrote:
I suspect you are concerned with the user changing the sheet ta
name
that
maybe used in your code? I always use the codename for th
worksheet,
that
way the user can change tabs and it does not affect the code. Yo
can
only
set the codename in the VBA editor or through VBA code itself.

If this is not the problem, then the only way I can think of, i
to
store
the sheet name on each sheet (perhpas a hidden location) and tes
the
current name against that ?

--
Cheers
Nigel



"T-®ex" wrot
in
message
...

Hi! Is there an event or a way to determine if a sheet's name
has
been
changed by the user?


--
T-®ex


------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399687



--
T-®ex

------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399687



--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=399687

Dear Nigel,

Thanks for your help!!!

You kept referring to 'codename'... I never realized there really is
CodeName property in VBA (you have to forgive me... i'm still
novice...) - that it is the '(Name)' property!!!

You've been a great help!!!

Thanks again!!!

Got ideas now!!! :

--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=39968

Glad to help - sorry we missed the point. As when I discovered the codename
property a lot of prior problems I had with users changing tab names
dissappeared. The other benefit is that you can use the sheet codename as a
reference to the object directly....

mySheet.Cells(1,2)
mySheet.Range("A1:C20").Sort Sortkey1:=mySheet.Range("A1")

etc.....

and of course the sheetname property is still available for keeping the user
informed about the sheet tabs they see and change!

--
Cheers
Nigel



"T-®ex" wrote in message
...

Dear Nigel,

Thanks for your help!!!

You kept referring to 'codename'... I never realized there really is a
CodeName property in VBA (you have to forgive me... i'm still a
novice...) - that it is the '(Name)' property!!!

You've been a great help!!!

Thanks again!!!

Got ideas now!!! :)


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=399687

Thanks again!!! ;)

Nigel Wrote:
Glad to help - sorry we missed the point. As when I discovered th
codename
property a lot of prior problems I had with users changing tab names
dissappeared. The other benefit is that you can use the sheet codenam
as a
reference to the object directly....

mySheet.Cells(1,2)
mySheet.Range("A1:C20").Sort Sortkey1:=mySheet.Range("A1")

etc.....

and of course the sheetname property is still available for keeping th
user
informed about the sheet tabs they see and change!

--
Cheers
Nigel



"T-®ex" wrote i
message
...

Dear Nigel,

Thanks for your help!!!

You kept referring to 'codename'... I never realized there really i
a
CodeName property in VBA (you have to forgive me... i'm still a
novice...) - that it is the '(Name)' property!!!

You've been a great help!!!

Thanks again!!!

Got ideas now!!! :)


--
T-®ex

------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread
http://www.excelforum.com/showthread...hreadid=399687


--
T-®e
-----------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657
View this thread: http://www.excelforum.com/showthread.php?threadid=39968