Excel VBA

  • Thread starter Thread starter EdT
  • Start date Start date
E

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
 
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 (e-mail address removed)
 
EdT said:
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
Click to expand...
ActiveSheet.Name seems to work OK for that.
 
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
 
Back
Top