hide / unhide names

[Michael Beckinsale]

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

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

 

[Tom Ogilvy]

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

 

[Michael Beckinsale]

Norman / Tom

Many thanks for your prompt replies.

The code that you sent will be very useful

Regards

 

[Walt]

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