I am looping through all sheets in a workbook and come across a sheet
that is named the same a one of the code modules and is
xlsheetveryhidden. I changed the code module name with VBE and re-ran
the code. The name changed to the new name. How could this be
possible?

I believe this is a very old workbook. Could this have anything to do
with macrosheets? The sheet does not have a codename.

"CG" <(E-Mail Removed)> wrote in message
news:309f12e0-329d-46c4-900d-(E-Mail Removed)...
>I am looping through all sheets in a workbook and come across a sheet
> that is named the same a one of the code modules and is
> xlsheetveryhidden. I changed the code module name with VBE and re-ran
> the code. The name changed to the new name. How could this be
> possible?
>
> I believe this is a very old workbook. Could this have anything to do
> with macrosheets? The sheet does not have a codename.

This is typical of workbooks with VBA code modules that were created in
Excel 5/95. To convert the module into a current version code module, the
easiest thing to do is to first make sure the module has a valid code module
name (no spaces or non alphanumeric characters). Then export the module,
remove it from the project and import it back into the project. A new
version module will be created upon import and the module name should then
disappear from the Sheets collection.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

On Jun 2, 1:11 pm, "Rob Bovey" <Rob_Bo...@msn.com> wrote:
> "CG" <CGJ...@gmail.com> wrote in message
>
> news:309f12e0-329d-46c4-900d-(E-Mail Removed)...
>
> >I am looping through allsheetsin a workbook and come across a sheet
> > that is named the same a one of thecodemodules and is
> > xlsheetveryhidden. I changed thecodemodulename with VBE and re-ran
> > thecode. The name changed to the new name. How could this be
> > possible?
>
> > I believe this is a very old workbook. Could this have anything to do
> > with macrosheets? The sheet does not have a codename.
>
> This is typical of workbooks with VBAcodemodules that were created in
> Excel 5/95. To convert themoduleinto a current versioncodemodule, the
> easiest thing to do is to first make sure themodulehas a validcodemodule
> name (no spaces or non alphanumeric characters). Then export themodule,
> remove it from the project and import it back into the project. A new
> versionmodulewill be created upon import and themodulename should then
> disappear from theSheetscollection.
>
> --
> Rob Bovey, Excel MVP
> Application Professionalshttp://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

Rob,

Thanks for the solution to fix the code. Now the question is, how can
one test if a member of the sheet colleciton is an old vbacodemodule?
Do you just do a test on type with an on error continue and test the
err.description or is there a better way?

Carl

On Jun 2, 1:11 pm, "Rob Bovey" <Rob_Bo...@msn.com> wrote:
> "CG" <CGJ...@gmail.com> wrote in message
>
> news:309f12e0-329d-46c4-900d-(E-Mail Removed)...
>
> >I am looping through allsheetsin a workbook and come across a sheet
> > that is named the same a one of thecodemodules and is
> > xlsheetveryhidden. I changed thecodemodulename with VBE and re-ran
> > thecode. The name changed to the new name. How could this be
> > possible?
>
> > I believe this is a very old workbook. Could this have anything to do
> > with macrosheets? The sheet does not have a codename.
>
> This is typical of workbooks with VBAcodemodules that were created in
> Excel 5/95. To convert themoduleinto a current versioncodemodule, the
> easiest thing to do is to first make sure themodulehas a validcodemodule
> name (no spaces or non alphanumeric characters). Then export themodule,
> remove it from the project and import it back into the project. A new
> versionmodulewill be created upon import and themodulename should then
> disappear from theSheetscollection.
>
> --
> Rob Bovey, Excel MVP
> Application Professionalshttp://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

Rob,

Thanks for the solution to fix the code. Now the question is, how can
one test if a member of the sheet colleciton is an old vbacodemodule?
Do you just do a test on type with an on error continue and test the
err.description or is there a better way?

Carl

<(E-Mail Removed)> wrote in message
news:9d05cd55-ac7b-4f93-b67b-(E-Mail Removed)...
> Thanks for the solution to fix the code. Now the question is, how can
> one test if a member of the sheet colleciton is an old vbacodemodule?
> Do you just do a test on type with an on error continue and test the
> err.description or is there a better way?

Hi Carl,

Assuming you're looping the sheets in something like the following
manner, you can use the TypeName function to check for modules. The only
time the type name "Module" will ever appear is if you've got an Excel 5/95
code module in the Sheets collection.

Dim objSheet As Object
For Each objSheet In ActiveWorkbook.Sheets
If TypeName(objSheet) = "Module" Then
MsgBox objSheet.Name & " is a legacy code module."
End If
Next objSheet

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

On Jun 3, 7:25 am, "Rob Bovey" <Rob_Bo...@msn.com> wrote:
> <deb.gen...@gmail.com> wrote in message
>
> news:9d05cd55-ac7b-4f93-b67b-(E-Mail Removed)...
>
> > Thanks for the solution to fix the code. Now the question is, how can
> > one test if a member of the sheet colleciton is an old vbacodemodule?
> > Do you just do a test on type with an on error continue and test the
> > err.description or is there a better way?
>
> Hi Carl,
>
> Assuming you're looping the sheets in something like the following
> manner, you can use the TypeName function to check for modules. The only
> time the type name "Module" will ever appear is if you've got an Excel 5/95
> code module in the Sheets collection.
>
> Dim objSheet As Object
> For Each objSheet In ActiveWorkbook.Sheets
> If TypeName(objSheet) = "Module" Then
> MsgBox objSheet.Name & " is a legacy code module."
> End If
> Next objSheet
>
> --
> Rob Bovey, Excel MVP
> Application Professionalshttp://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

Rob,

Thanks a million, just what I needed!

Carl