I want to refer to sheets' codenames that are set up in the active book from
another book (or an add-in)

e.g. MsgBox ActiveWorkbook.SheetCodeNameHere.Cells(6, 2).Value

Can I do that?


M

Hello Michelle:
One good way to test syntax is to use the immediate window when you are in
the VBA interface. It will answer syntax questions.

Just enter it in the immediate window as:

MsgBox (ActiveWorkbook.Sheets("YourSheetName").Cells(6, 2).Value)
--
Rich Locus
Logicwurks, LLC


"Michelle" wrote:

> I want to refer to sheets' codenames that are set up in the active book from
> another book (or an add-in)
>
> e.g. MsgBox ActiveWorkbook.SheetCodeNameHere.Cells(6, 2).Value
>
> Can I do that?
>
>
> M
>

I am not sure I understood your query correctly.

Edit the workbookname as displayed on the caption...Using the workbook
object you can access any open workbook

Sub Macro()
Dim wb As Workbook
Set wb = Workbooks("book1.xls")
MsgBox wb.Worksheets("Sheet1").Range("A1")
End Sub

--
Jacob (MVP - Excel)


"Michelle" wrote:

> I want to refer to sheets' codenames that are set up in the active book from
> another book (or an add-in)
>
> e.g. MsgBox ActiveWorkbook.SheetCodeNameHere.Cells(6, 2).Value
>
> Can I do that?
>
>
> M
>

Michelle formulated the question :
> I want to refer to sheets' codenames that are set up in the active book from
> another book (or an add-in)
>
> e.g. MsgBox ActiveWorkbook.SheetCodeNameHere.Cells(6, 2).Value
>
> Can I do that?
>
>
> M

Here's a reusable function that will return the sheetname for a sheet
in any open workbook, by passing it a ref to the workbook and the
codename of the sheet.

Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As
String
Dim wks As Worksheet
For Each wks In Wkb.Worksheets
If wks.CodeName = CodeName Then Get_SheetTabName = wks.name: Exit
Function
Next
End Function

Note that the If line is one line, so watch the word wrap.

HTH
Garry

That's more like it! thank you.

M


"GS" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Michelle formulated the question :
>> I want to refer to sheets' codenames that are set up in the active book
>> from another book (or an add-in)
>>
>> e.g. MsgBox ActiveWorkbook.SheetCodeNameHere.Cells(6, 2).Value
>>
>> Can I do that?
>>
>>
>> M
>
> Here's a reusable function that will return the sheetname for a sheet in
> any open workbook, by passing it a ref to the workbook and the codename of
> the sheet.
>
> Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As String
> Dim wks As Worksheet
> For Each wks In Wkb.Worksheets
> If wks.CodeName = CodeName Then Get_SheetTabName = wks.name: Exit
> Function
> Next
> End Function
>
> Note that the If line is one line, so watch the word wrap.
>
> HTH
> Garry
>
>