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-容x" 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-容x" 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-容x
------------------------------------------------------------------------
T-容x's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399687
--
T-容x
------------------------------------------------------------------------
T-容x's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399687
--
T-容x
------------------------------------------------------------------------
T-容x's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399687