Using Worksheet Codename to Determine Worksheet Existence
 

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

Using Worksheet Codename to Determine Worksheet Existence
 

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" wrote:

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

Using Worksheet Codename to Determine Worksheet Existence
 

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" wrote in message
...
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" wrote:

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

Using Worksheet Codename to Determine Worksheet Existence
 

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" wrote:

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" wrote in message
...
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" wrote:

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

Using Worksheet Codename to Determine Worksheet Existence
 

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



"keepITcool" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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