Ian, I wrote a routine to loop through all my operating instruction
workbook files (over 300) and change all the workbook-wide names to local
names. That cut back drastically on the number of names being looped thru
using the routine you listed below. Everything's working great now .... the
routine is truly transparent to the end user.
Thanks to everyone for their help.
From: Ian Murphy [mailto:[log in to unmask]]
Sent: Tuesday, August 05, 2003 5:36 AM
To: [log in to unmask]
Subject: Re: Named Ranges Only To Trigger Actions
Probably too late but anyway. The following should be both fast and
unnoticable to the user
Private Sub Worksheet_Change(ByVal rRng As Range)
Dim oName As Name
For Each oName In ActiveWorkbook.Names
If Not Intersect(rRng, oName.RefersToRange) Is Nothing Then
MsgBox ("A hit on " & oName.Name)
'MsgBox ("A miss " & oName.Name)
It (too) steps through each of the named ranges defined in the workbook.
Running through a list of 200 on a Pentium3 should take no more than a
fraction of a second... meaning that the user should not notice. If it
takes longer it will be because of other problems.
--- "Gafford, William L" <[log in to unmask]> wrote: > I've got a
situation where I've got a lot of workbooks created that
> used as production operating instructions for our manufacturing lines.
> workbooks all contain about 15 sheets, with named ranges where actual
> operating condition data is entered. I'd like to write the range name
> the value entered to a text file when an entry is made into a named
> range. I
> would use the worksheet change event to initiate this. What's the
> way to have the worksheet detect if an entry is made into a named range?
> I've got a routine from the Microsoft knowledge base that can do this
> but it
> basically loops through every named range (until one is found that
> entry) but it's too slow. I've got about 200 named ranges in the
> and if a user is editing this workbook, to have that macro run every
> time an
> entry is made would be torture! Thanks! I've got to have this done by
> Monday, so a timely response would be appreciated!
Want to chat instantly with your online friends? Get the FREE Yahoo!