Phantom range name

[Giordano Bruno]

I have an excel worksheet and if I combine two or more contiguous cells and give them a name, the name appears centered over the range with a border around the range. Above about 35% zoom, the image disappears. but reappears when the zoom is reduced again. The characters are of a size that occupies most of the range. It is not selectable and when I tried to create a mini sheet using xl2bb the resulting image doesn't display the name. If anyone has had this experience, I'd like to know if you found a way of preventing this unusual behaviour.

 

[Peter_SSs]

The only way that I know is to make the name(s) invisible using vba. You could make individual names Invisible/Visible or all of them like this

Sub Hide_Names()
  Dim Nm As Name
  
  For Each Nm In ThisWorkbook.Names
    Nm.Visible = False
  Next Nm
End Sub

Note too that if made invisible the names will also disappear from the Name Manager even though the Named Ranges themselves still exist.

BTW, below 40% zoom is when the names appear.

 

[Peter_SSs]

This might be more useful - toggling them all off/on

ub Hide_Names_v2()
  Dim Nm As Name
  
  For Each Nm In ThisWorkbook.Names
    Nm.Visible = Not Nm.Visible
  Next Nm
  Application.ScreenUpdating = True
End Sub

 

[Giordano Bruno]

Hi Peter,

You're even smarter than ChatGPT. Certainly smarter tham me as I'm using similar code to hide all names for when I distribute the model and yet I didn't think of that as a solution. I copied your code and, oddly, I was unable to step though it. I put a shape on the sheet and attached it to the code and when I clicked the shape, all of the rectangular borders disappeared along with the text. When I click it again, they all come back

My heartfelt thanks for the solution - sadly I can't stand you a beer.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.