Can I refer to a sheet in another book by its codename?

[Michelle]

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

 

[Rich Locus]

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)

 

[Simon Lloyd]

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

I want to refer

to sheets' codenames that are set up in the active book fro
another book (or an add-in

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

Can I do that


M


--
Simon Lloyd

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Jacob Skaria]

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

 

[GS]

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

 

[Michelle]

That's more like it! thank you.

M

Michelle formulated the question :

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