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

You reference a sheet codename like this Sheet(1) however, you cannot
use a sheet codename when you reference a Sheet that is in a different
Workbook to the one that the code is in.

Michelle;723812 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


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=202735

http://www.thecodecage.com/forumz

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