Hi,

Is there a method for hiding a sheet via the codename, when the
codename is not hard coded but derived from cells in a spreadsheet?

Eg. I would like to loop through cells which contain codenames. If
the codename is ticked, then I want to run a sub to hide that sheet,
using that codename. By way of illustration:

Set rng = Sheets("Lookups").Range("A1:A20")
For Each cell In rng
Set x = ThisWorkbook.VBProject.VBComponents(cell)
x.Visible = xlSheetHidden
Next cell

I'm not sure why this throws up error 438 at the line:
x.visible = xlSheetHidden

I've tried various permuations but can't get it to work. Many thanks in
advance if anyone kindly provides a solution.

Regards,
Chris Adams

Not sure what you are trying to do, but the VBProject.VBComponents is for
the components of VBA IDE.
You cannot hide them.

Are you trying to hide some worksheet(s) ?

NickHK


egroups.com...
Hi,

Is there a method for hiding a sheet via the codename, when the
codename is not hard coded but derived from cells in a spreadsheet?

Eg. I would like to loop through cells which contain codenames. If
the codename is ticked, then I want to run a sub to hide that sheet,
using that codename. By way of illustration:

Set rng = Sheets("Lookups").Range("A1:A20")
For Each cell In rng
Set x = ThisWorkbook.VBProject.VBComponents(cell)
x.Visible = xlSheetHidden
Next cell

I'm not sure why this throws up error 438 at the line:
x.visible = xlSheetHidden

I've tried various permuations but can't get it to work. Many thanks in
advance if anyone kindly provides a solution.

Regards,
Chris Adams

I'm not sure why this throws up error 438 at the line:
x.visible = xlSheetHidden

Because x is a VBComponent not a worksheet.

Sub a()
Dim x As VBComponent
Dim ShName As String
Set x = ThisWorkbook.VBProject.VBComponents("CodeName")
ShName = x.Properties("Name")
Worksheets(ShName).Visible = xlSheetHidden
End Sub


--
Jim
wrote in message
oups.com...
| Hi,
|
| Is there a method for hiding a sheet via the codename, when the
| codename is not hard coded but derived from cells in a spreadsheet?
|
| Eg. I would like to loop through cells which contain codenames. If
| the codename is ticked, then I want to run a sub to hide that sheet,
| using that codename. By way of illustration:
|
| Set rng = Sheets("Lookups").Range("A1:A20")
| For Each cell In rng
| Set x = ThisWorkbook.VBProject.VBComponents(cell)
| x.Visible = xlSheetHidden
| Next cell
|
| I'm not sure why this throws up error 438 at the line:
| x.visible = xlSheetHidden
|
| I've tried various permuations but can't get it to work. Many thanks in
| advance if anyone kindly provides a solution.
|
| Regards,
| Chris Adams
|

That did the trick - many thanks for your replies Nick and Jim.
Especially helpful Jim because I'd tried to use .properties in various
ways and I couldn't find an applicable syntax in the VBA help or
through the archives of this group.

Thanks again for your time - much appreciated,
Chris Adams