Using Worksheet Codename to Determine Worksheet Existence

[David Copp]

Greetings,

I've got a worksheet codename (activeworkbook.activesheet.codename) and wish
to use it to determine if the worksheet exists at a later point in time.
Presuming I've stored the codename, looking for a way to see if worksheet
exists (i.e. open) without knowing it's "friendly" name or worksheet index.
Any ideas?

Is it possible to get "friendly" name from codename? (then check friendly
name)
Other methods welcome.

Thanks,

Dave
(e-mail address removed)

 

[keepITcool]

David,

you'll have to add some errorhandling yourself,
but this works for me:

Sub tst()
MsgBox Code2Name(ActiveWorkbook, "sheet2")
End Sub
Function Code2Name(wb As Workbook, sCodeName As String)
Dim c As New Collection, sh
For Each sh In wb.Sheets
c.Add sh.Name, sh.CodeName
Next
Code2Name = c(sCodeName)
End Function




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[David Copp]

Hi,

Thanks for the option. I was aware of this method but I'm looking for a
direct method instead of iterating through all open workbooks. Thoughts?

Thanks,

Dave

 

[keepITcool]

Can't imagine..

100 books
100 sheets in each book

=> .15 secs

and why do you need to iterate thru workbooks?

there's a direct way too, BUT your user needs to enable
Access to Visual Basic Project


Function CodeToName(wb As Workbook, codename As String) As String
Dim vbp As Object
On Error Resume Next
Set vbp = wb.VBProject
If Err = 1004 Then
MsgBox _
"Please enable:" & vbNewLine & _
"'Trust access to Visual Basic Project'" & vbNewLine & _
"in Tools/Macro/Security (2ndtab)", vbExclamation, "Error"

Exit Function
End If
With vbp.vbcomponents(codename)
CodeToName = .Properties("Name") ' .Properties("Index")
End With

End Function




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[David Copp]

Thanks... it appears my assumption of a strong performance penalty may be in
correct... will test asap..
Enabling Visual Project option is unattractive.

Thank you again

Dave