[EdT]

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

[Chip Pearson]

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

[Jerry Park]

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.

[Mark D[_3_]]

Accessing the new sheet should be as simple as reffering to it by its name
using the sheets property ie.

Sheets("New Sales")

It won't matter wont the sheet number is this way and you can import as many
as you like (currently using a similiar systen for timetables)

Mark D

"Ed Tess" wrote in message
...

Thank you for the reply. However I left out one vital piece of
information in my original message. Guess I got nervous being my first
time.

I have created a workbook our sales department uses for pricing. It has
about 40 separate workbooks (each containing only one sheet) that are
moved into the base workbook as needed. The sheets in the individual
workbooks have code in the sheet module. Problem is when the workbook is
opened to move it into the base workbook the "Enable/Disable: macros
screen pops-up.

My thought was to delete the code in the individual workbook sheets and
use my VBA code to add the code to the sheet module once it is moved to
the base workbook. I figured out how to insert the code by using the
"Sheet1" reference. I would like to use the name of the sheet as the
reference but cannot figure out the proper object calls to get to it.

I will take a look at the sites you referenced in your reply.

Thanks again for the help,
Ed T

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!