If the InStr Function finds “Lookup” with in the text, it returns a numerical value, which represents the character position of where “Lookup” starts within the text string. I use the VBA Function InStr, which stands for In String, to check and see if the Name Property of each Name has “Lookup” contained in it, by starting at character 1. If InStr(1, n.Name, "Lookup", vbTextCompare) > 0 Then
#Formula for veryhidden in excel on mac code#
In the code below I’m looping through the Names collection, setting the Visible property to False, then checking to see if any have “Lookup” in the Name property string and making those Visible. Sometimes you may want to leave a few of the Names visible for users. If n.Visible = False Then n.Visible = TrueĮnd Sub Keep Some Names Visible with Code The following VBA code will do just that.
While working on a file, it can sometimes be advantageous to actually SEE ALL the Names. If the Name’s corresponding range address is on a hidden worksheet, this won’t work. Or use the keyboard shortcut Ctrl+G or F5 to bring up the Go To dialog box, type a Name in the Reference text box, and click OK. You can still Go To a Name that is hidden, just type a Name in the Name Box and hit enter. Just change out “MachName” for each Name you want to hide. If you just want to hide a few Names, the code below, executed in the Immediate Window of the VBA Editor, will do the trick. If you have a great many names to hide, it’s best to loop through the Names collection and change the Visible property value to TRUE for all Names.Īfter running this code, you now have a blank Name Box. You can hide a Name by changing the Visible property to FALSE, but only in the VBA Editor. Just check out the Name Box in the picture to the right.Įach Name has a Visible property that’s set to TRUE by default. If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many.
0 kommentar(er)
