Glad you got it working.
This did work for me.
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
but this wouldn't:
Set myWks = .Worksheets(.VBProject.VBComponents.Item("Sheet1") .Value)
Any chance you mixed/matched/inserted/delete .properties() when you were
testing??
Doug Glancy wrote:
Thanks Dave,
There I was throwing around the phrase"code name" but never noticed it as a
property!
BTW, I had to modify one line in version 1 to make it work for me.
Otherwise only worked when code name and tab name were the same. I got the
modification below by looking at Rob's answer. I'm not quite sure what the
difference is, but ".Name" doesn't seem to be a allowed property in this
context:
Set myWks =
.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Properties("Name").Value)
Thanks again,
Doug
"Dave Peterson" wrote in message
...
If you have xl2002 (or higher) and you have
tools|macro|security|Trusted sources tab|trust access to VBA Project
unchecked, then the first method won't work:
Option Explicit
Sub testme01()
Dim myWks As Worksheet
Set myWks = Nothing
With Workbooks("book1.xls")
On Error Resume Next
Set myWks =
.Worksheets(.VBProject.VBComponents.Item("Sheet1") .Name)
If Err.Number < 0 Then
MsgBox "something went wrong"
Err.Clear
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
On Error GoTo 0
End With
End Sub
But you can loop through the worksheets and find a match:
Sub testme02()
Dim wks As Worksheet
Dim myWks As Worksheet
Set myWks = Nothing
For Each wks In Workbooks("book1.xls").Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Set myWks = wks
Exit For
End If
Next wks
If myWks Is Nothing Then
MsgBox "not found"
Else
MsgBox myWks.Name & vbLf & myWks.Range("a1").Value
End If
End Sub
Doug Glancy wrote:
My 3rd try on this question, hopefully the charm...
There was discussion last week of the advantage of referring to a
worksheet
by it's code name, e.g., Sheet1 rather than it's tab name, as in
WorkSheets("mysheet") or by index, as in WorkSheets(1). My question
is how to refer to a sheet in another workbook. I've tried:
Workbooks("mybook").Sheet1 and other such variations, without success.
I can access a sheet by code name in another workbook as a VBComponent
e.g.,
Workbooks("Book1").VBProject.VBComponents.Item("Sh eet1")
but then I can't, for example, refer to a range within the component.
Thanks in advance for any help a with this,
Doug
--
Dave Peterson
--
Dave Peterson