Reference question

[wolf.stalker]

Hi all and thanks for any help you can provide...

i have a module that is outside my workbook (lives on a custom toolbar actually). that module is trying to reference a number of worksheets when the workbook is open.

my problem is that i do not know how to reference my sheets in this workbook! i keep getting an errors no matter how i try and reference them. like, "object dosen't support this property or method" when trying something like "ActiveWorkbook.sheet16.Range("L" & StartPostingCell)"


what is the absolute path to say sheet16 that is in workbook named "Mater Workbook.xls" ?

again, thanks for your help.

-will

[Bob Phillips]

When using a workbook qualifier, you cannot use a sheet codename (shame, but there you go), you have to reference through the worksheets collection.

[wolf.stalker]

When using a workbook qualifier, you cannot use a sheet codename (shame, but there you go), you have to reference through the worksheets collection.

so, how exactly would i do it then? on a side note, i know you can reference the sheet using it's index number or by using it's given name of the sheet, but both are not what i want to do for fear of change down the road.

soooo...how does write a reference ?

[Bob Phillips]

You get no choice I am afraid.

What I tend to do is when I open the workbook I build an array of codename to sheetnames, and then use the value from there, but it can't stop a user changing the sheetname and f#*?ing up, but at least it works okay again next time.

[wolf.stalker]

You get no choive I am afraid.

What I tend to do is when I open the workbook I build an array of codename to sheetnames, and then use the value from there, but it can't stgop a user changing the sheetname and f#*?ing up, but at least it works okay again next time.

yeah...well, nice to see the guru's at microshaft will have at least one thing to fix at some point. forgive me, but with all that you CAN do, i am just surprised that something as simple as saying or coding "hey, grab sheet #16 and put this in cell G155" is not as simple as one might think. man....

ok! so to get around this, i need to build an array of sheetnames for my workbook. i will have to brush up on my array skills as i probably dont use them nearly enough.

thanks for your .02.

[Bob Phillips]

Maybe this will help you

[vba]

Dim XLSheetname As String
Dim VBDataSheet As Worksheet

XLSheetname = ThisWorkbook.VBProject.VBComponents("the codename").Properties("Name")

With ThisWorkbook
Set VBDataSheet = .Worksheets(XLSheetname)
End With
[/vba]