Hide sheet via codename

[chris_j_adams]

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

 

[NickHK]

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

 

[Jim Rech]

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
| 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
|

 

[chris_j_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