I was just explaining the reson for your error. You weren't passing a string
to the function. Now your 2nd problem is getting a string. I would used a
REFEDIT control. REFEDIT is only available on a VBA userform. I twould
allow you to select a range of cells and it would return a string if the form
you are looking for.



"Clayman" wrote:

Thanks for the quick reply! But...

Yeah - that would make it a string. But I'd like to make it a clickable
range so the user can enter "=sheetname(" then click on the cell for the
reference.

--
Adios,
Clay Harryman


"Joel" wrote:

You forgot th edouble quotes

=sheetname("Sheet1!A1")


"Clayman" wrote:

It's two - two - two questions in one!

I am writing a formula to extract the sheet name from a cell reference.
Here's my code:

Function sheetname(sell As String) As String
exclpoint = InStr(sell, "!")
sheetname = Left$(sell, exclpoint - 1)
End Function

I've tested this code in the following sub and it worked:

Private Sub cheetname()
sell = "Sheet1!A1"
exclamationpoint = InStr(sell, "!")
thename = Left$(sell, exclamationpoint - 1)
okeedokee = MsgBox(thename, vbOKOnly)
End Sub

But the function gives me a #NAME? error. So, my thinking is that I need to
define this as a range instead of a string. But how do I convert the range
type to a string?

Or, is the #NAME? error 'cuz I'm not calling the formula correctly?
=sheetname(Sheet1!A1)
--
Adios,
Clay Harryman