When in the VBA IDE the sheets in the workbook under Microsoft Excel
Objects. Each worksheet is numbered (Sheet1, Sheet2,....) and next to
the sheet number is the name of the sheet. Is there anyway to access
the sheet module by the name of the sheet rather than the sheet
number?

....Item("Sheet1")...

....Item("Sales Leads")...

Any help would be greatly appreciated.

Thanks,
EdT

Ed,

The sheet name outside the parentheses is called the CodeName of
sheet. You can refer directly to it in your VBA code. E.g.,

Sheet1.Range("A1").Value = 123

This code will work properly even if the user renames the
worksheet.

The CodeName is also the name of the VBComponent object that
represents that worksheet in the VBE's object model. See
http://www.cpearson.com/excel/vbe.htm and
http://www.cpearson.com/excel/codemods.htm for details about
working with VBComponents.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"EdT" wrote in message
om...
When in the VBA IDE the sheets in the workbook under Microsoft
Excel
Objects. Each worksheet is numbered (Sheet1, Sheet2,....) and
next to
the sheet number is the name of the sheet. Is there anyway to
access
the sheet module by the name of the sheet rather than the sheet
number?

...Item("Sheet1")...

...Item("Sales Leads")...

Any help would be greatly appreciated.

Thanks,
EdT

EdT wrote:
When in the VBA IDE the sheets in the workbook under Microsoft Excel
Objects. Each worksheet is numbered (Sheet1, Sheet2,....) and next to
the sheet number is the name of the sheet. Is there anyway to access
the sheet module by the name of the sheet rather than the sheet
number?

...Item("Sheet1")...

...Item("Sales Leads")...

Any help would be greatly appreciated.

Thanks,
EdT
ActiveSheet.Name seems to work OK for that.