I need code to select a specific worksheet. I can not use the name on the
tab since I have code which renames the worksheet based on data input.
Currently if you look at the VBA Project the name is Sheet1(01 Jan 09). It
is the 01 Jan 09 which can change, but the Sheet1 stays consistent. So how
can I select the worksheet based on the Sheet1

for each ws in worksheets
if ws.codename = "Sheet1" then
ws.activate
exit for
end if
next

untested for typos, apart from that it should work except -

Note when you insert a new sheet it's codename is not updated if the VBE is
closed until the wb is saved, closed & reopened.


Regards,
Peter T

"Patrick C. Simonds" wrote in message
...
I need code to select a specific worksheet. I can not use the name on the
tab since I have code which renames the worksheet based on data input.
Currently if you look at the VBA Project the name is Sheet1(01 Jan 09). It
is the 01 Jan 09 which can change, but the Sheet1 stays consistent. So how
can I select the worksheet based on the Sheet1

for each ws in worksheets
if ws.codename = "Sheet1" then
ws.activate
exit for
end if
next

No need for all that. If you know the code name of the worksheet you
can use it directly:

Sheet11.Activate ' works regardless of sheet Name and Index

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Sat, 15 Nov 2008 10:08:05 -0000, "Peter T" <peter_t@discussions
wrote:

for each ws in worksheets
if ws.codename = "Sheet1" then
ws.activate
exit for
end if
next

untested for typos, apart from that it should work except -

Note when you insert a new sheet it's codename is not updated if the VBE is
closed until the wb is saved, closed & reopened.


Regards,
Peter T

"Patrick C. Simonds" wrote in message
...
I need code to select a specific worksheet. I can not use the name on the
tab since I have code which renames the worksheet based on data input.
Currently if you look at the VBA Project the name is Sheet1(01 Jan 09). It
is the 01 Jan 09 which can change, but the Sheet1 stays consistent. So how
can I select the worksheet based on the Sheet1

"Chip Pearson" wrote in message
...
for each ws in worksheets
if ws.codename = "Sheet1" then
ws.activate
exit for
end if
next

No need for all that. If you know the code name of the worksheet you
can use it directly:

Sheet11.Activate ' works regardless of sheet Name and Index

Indeed that works, but I think only if the sheet's codename is name is known
and can be hardcoded at design time and only within the same project (ie
can't reference a sheet that way in some other workbook without a reference)
and there could be compile problems if the sheet is subsequently deleted.

Regards,
Peter T

Indeed that works, but I think only if the sheet's codename is name is known
and can be hardcoded at design time

You can use code like the following:


Dim S As String
S = "Sheet3"
ThisWorkbook.VBProject.VBComponents(S).Activate




Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 15 Nov 2008 19:23:01 -0000, "Peter T" <peter_t@discussions
wrote:


"Chip Pearson" wrote in message
.. .
for each ws in worksheets
if ws.codename = "Sheet1" then
ws.activate
exit for
end if
next

No need for all that. If you know the code name of the worksheet you
can use it directly:

Sheet11.Activate ' works regardless of sheet Name and Index

Indeed that works, but I think only if the sheet's codename is name is known
and can be hardcoded at design time and only within the same project (ie
can't reference a sheet that way in some other workbook without a reference)
and there could be compile problems if the sheet is subsequently deleted.

Regards,
Peter T