hide / unhide names

Hi,

Whilst reviewing a spreadsheet created by somebody else l have come across
something l have never seen before !

Hidden range names

Can somebody please tell me :

a) How do you hide range names
b How do you unhide range names

I have carefully inspected the VBA Modules in the workbook and there isn't
any code relating to hiding / unhiding range names. The cells containing the
hidden names have not been hidden using Cells,Protection,Hide and anyway the
worksheets / workbook are not protected.

I can identify and unhide the using Karl Patrese's Name Manager but l would
like to know how its done. On reviewing the VBA code contained in the
workbook l think it is unlikely that the original author hid some 40 codes
spread across many randon cells and worksheets by code then deleted it.

All contributions gratefully received

Regards

Michael Beckinsale

Hi Michael,

> a) How do you hide range names

Sub CreateHiddenName()
ActiveWorkbook.Names.Add "MyName", _
Worksheets("Sheet1").Range("A1:A10")
ActiveWorkbook.Names("MyName").Visible = False

End Sub

> b How do you unhide range names

Sub MakeAllNamesVisible()
Dim NM As Name

For Each NM In ActiveWorkbook.Names
NM.Visible = True
Next

End Sub

---
Regards,
Norman



"Michael Beckinsale" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Whilst reviewing a spreadsheet created by somebody else l have come across
> something l have never seen before !
>
> Hidden range names
>
> Can somebody please tell me :
>
> a) How do you hide range names
> b How do you unhide range names
>
> I have carefully inspected the VBA Modules in the workbook and there isn't
> any code relating to hiding / unhiding range names. The cells containing
> the
> hidden names have not been hidden using Cells,Protection,Hide and anyway
> the
> worksheets / workbook are not protected.
>
> I can identify and unhide the using Karl Patrese's Name Manager but l
> would
> like to know how its done. On reviewing the VBA code contained in the
> workbook l think it is unlikely that the original author hid some 40 codes
> spread across many randon cells and worksheets by code then deleted it.
>
> All contributions gratefully received
>
> Regards
>
> Michael Beckinsale
>
>

the only way I know is to do it with code.

Sub MakeHidden()
Dim nm as Name
For Each nm In ThisWorkbook.Names
nm.Visible = False
Next
End Sub

--
Regards,
Tom Ogilvy

"Michael Beckinsale" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Whilst reviewing a spreadsheet created by somebody else l have come across
> something l have never seen before !
>
> Hidden range names
>
> Can somebody please tell me :
>
> a) How do you hide range names
> b How do you unhide range names
>
> I have carefully inspected the VBA Modules in the workbook and there isn't
> any code relating to hiding / unhiding range names. The cells containing
the
> hidden names have not been hidden using Cells,Protection,Hide and anyway
the
> worksheets / workbook are not protected.
>
> I can identify and unhide the using Karl Patrese's Name Manager but l
would
> like to know how its done. On reviewing the VBA code contained in the
> workbook l think it is unlikely that the original author hid some 40 codes
> spread across many randon cells and worksheets by code then deleted it.
>
> All contributions gratefully received
>
> Regards
>
> Michael Beckinsale
>
>

Norman / Tom

Many thanks for your prompt replies.

The code that you sent will be very useful

Regards

"Michael Beckinsale" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Whilst reviewing a spreadsheet created by somebody else l have come across
> something l have never seen before !
>
> Hidden range names
>
> Can somebody please tell me :
>
> a) How do you hide range names
> b How do you unhide range names
>
> I have carefully inspected the VBA Modules in the workbook and there isn't
> any code relating to hiding / unhiding range names. The cells containing
the
> hidden names have not been hidden using Cells,Protection,Hide and anyway
the
> worksheets / workbook are not protected.
>
> I can identify and unhide the using Karl Patrese's Name Manager but l
would
> like to know how its done. On reviewing the VBA code contained in the
> workbook l think it is unlikely that the original author hid some 40 codes
> spread across many randon cells and worksheets by code then deleted it.
>
> All contributions gratefully received
>
> Regards
>
> Michael Beckinsale
>
>

Hi Michael,

I can understand your puzzlement. A few years back I saw a number of cases
that might explain what you've observed in the following: "On reviewing the
VBA code contained in the workbook l think it is unlikely that the original
author hid some 40 codes spread across many random cells and worksheets by
code then deleted it."

All of those cases involved spreadsheets originally developed in Lotus 123
and converted to Excel. Some of the names persisted though the references
were no longer valid.

Glad you've had the other feedback and results. And, I hope this helps
explain the puzzle.

Best Regards,
Walt Weber