Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I refer to a sheet in another book by its codename?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I refer to a sheet in another book by its codename?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I refer to a sheet in another book by its codename?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I refer to a sheet in another book by its codename?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I refer to a sheet in another book by its codename?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I refer to a sheet in another book by its codename?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Sheet By Codename | Excel Programming | |||
How to refer to an other worksheet (or book) | Excel Programming | |||
How can I refer a particular Sheet in a work book from a word doc | Excel Programming | |||
Using a txt string to refer to other work book | Excel Worksheet Functions | |||
Q - Excel 2000: How to refer to worksheet in the same book? | Excel Worksheet Functions |