Convert a range t ype to a string type

[Clayman]

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)

 

[Joel]

You forgot th edouble quotes

=sheetname("Sheet1!A1")

 

[Clayman]

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.

 

[Joel]

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]

So you can't pass a range to a formula and extract a string from the range?

My user is asking for a way to just point-and-click to get the sheet name.

I do appreciate all your help.

 

[Dave Peterson]

Option Explicit
Function sheetname(Rng as range) As String
sheetname = rng.parent.name
End Function

=====

If the workbook has been saved, you could also use a formula like:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
to get the name of the sheet with the formula.

Or use a formula like this:
=MID(CELL("filename",'Sheet2'!A1),FIND("]",CELL("filename",'Sheet2'!A1))+1,255)
to get the name of a different sheet (I used Sheet2).

both of these will reevaluate if the worksheet name is changed.