"rm81" wrote:

hey,

so did the debug thing and it came up with what seems to me is a perfectly
sensible name - 2138085 :
GetSheetName():
sRefTo = ='2138085'!$A$1:$E$100
iPosEq = 1
iPosEx = 11
GetSheetName = '2138085'

Ouch, actually it seems as if there are little pesky quote characters as
well (') enclosing the actual sheet name (it's obvious when you look closely
at the output, which I obviously didn't -- ooops). So I suppose we have to
modify the code a bit:

GetSheetName = Mid$(sRefTo, iPosEq + 1, iPosEx - iPosEq - 1)

....will become:

GetSheetName = Mid$(sRefTo, iPosEq + 2, iPosEx - iPosEq - 3)

....and this will stop the quotes trickling through. To see the difference,
try running for example a little routine like this:

Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("sheet1") ' This is OKAY
Set ws2 = Worksheets("'sheet1'") ' This will FAIL due to the single
quote chars
End Sub

A couple of questions though - the result of "sRefTo" is the name of the
sheet plus just the range for Range "COMMANDE" i.e. $A$1:$E$100 - is that
normal?

Yes, it is. Also note that you can retireve info about the range the defined
name is refering to in serveral other ways, that is, you don't necessarily
have to use RefersTo -- you can use RefersToLocal, RefersToR1C1,
RefersToRange etc. Why not have a look in the VBA Help, especially the "See
Also" section of RefersTo, where many alternatives are listed.

Secondly - what do "iPosEq" and "iPosEx" refer to/mean/do? And why is
iPosEx 11?

Sorry, I should really be more explicit when I write example code: iPosEq
and iPosEx refer to different positions in the string we're searching, using
InStr().

- iPosEq will point to the location of the Equal sign (if one is present in
sRefTo)
- iPosEx will point to the Exclamation sign (hopefully)

We then use Mid$ to retrieve the part of the sRefTo string that should
represent the name of the sheet. Read about Mid$ in the VBA Help -- it's a
pretty useful function...

Cheers,

It's me that should be cheers/thanking you!! I need to buy you a virtual
drink after all this!!
rm81

heheheh :o)

Well, how about "later", then? Or "ta da"?
/MP

"rm81" wrote:

Hi there - me again!

That simply means that you've got a bunch of defined names, some of which
are no longer okay -- the #REF! tells us that the definitions are broken,
most likely because you've deleted the sheets which those definitions were
using (when you delete sheets the associated named ranges will not be
automatically removed). To fix this, just go to the Insert Name Define...
dialogue, and kill all the defined names that are no longer in use, i.e., the
broken ones.

That's what I figured so have done that and it all looks to be in order now.

So, let's have a look at the actual code then (and I'm not saying it's bug
free, but if there are any problems I'm sure we'll be able to figure them out
:o)

Wow - looking at all that code - I think I understand about 25% of it only!!
But seeing as I am pushed for time, I think I'll take time out after
completing the project to go through all of it.

So I transplanted the code into the application but a slight hitch has come
up. When it runs through the following :

Set oSheet = Worksheets(GetSheetName(oName.RefersTo))
oSheet.PageSetup.LeftFooter = StripSheetName(oName.Name)
oName.RefersToRange.PrintOut Preview:=True

it then refers and goes through the

Private Function GetSheetName(sRefTo As String)

but at the end of it, it says "run-time error 9" (well, to be honest it
actually said "erreur d'execution 9" - am working in France so have an OS in
french; not the easiest!) which having looked up means "Subscript Out Of
Range".
I read that this means that one of the named sheets doesn't exist as given
in the Named sheets routine. Unfortunately as I don't understand all of the
code, I can't resolve this error!! Do you have an idea at all?
Many thanks! :)
rm81