you can make the Rng argument optional and test to see if it is present. If
missing, return just the sheet name and, if not, return a string reference
that includes the sheet name and the range reference.

Also, you could make the function more generic by adding an additional
argument (the number of the sheet whose name you want returned). Then you
can return the sheet name for any sheet, not just the first one.

Function SheetName(SheetIndex As Long, Optional Rng As Variant) As String
If IsMissing(Rng) Then
SheetName = Worksheets(SheetIndex).Name
ElseIf InStr(1, Worksheets(SheetIndex).Name, " ", vbTextCompare) 0 Then
SheetName = "'" & Worksheets(SheetIndex).Name & "'" & _
"!" & Rng
Else: SheetName = Worksheets(SheetIndex).Name & "!" & Rng
End If

End Function


=sheetname(1,"$A$5:$B$6")
should return something like Sheet1!$A$5:$B$6
or 'Sheet 1'!$A$5:$B$6 if there is a space in the sheet name

=sheetname(1)
should return Sheet1 (name only w/o apostrophes or exclamation mark.




"RichP" wrote:


Thanks for all the help it has worked a treat. I am now trying to break
the code down to understand what each part does.
Can the sheet name be displayed in a cell in the workbook?


--
RichP
------------------------------------------------------------------------
RichP's Profile: http://www.excelforum.com/member.php...o&userid=25267
View this thread: http://www.excelforum.com/showthread...hreadid=387620