Using the Excel Name box

Topic

New Reply

Hi

Here are some useful shortcuts I use with the Excel Name box:

•type the name of your macro in the Name box and press [Enter] to go straight to the corresponding code in the vba editor

•type 5:10 in the Name box and press [Enter] to select that row range

•type f:h in the Name box and press [Enter] to select that column range

•type b1:b10,d1:d10 in the Name box and press [Enter] to select those ranges

zeddy

Reply | Quote

Replies

A couple of others:

1. Create a Named Range: Select a range of cells > Type a name for the Range > Enter
2. Change an objects name: Select an Object (ex button) > change its name in the Name Box > Enter
3. Find Intersect between 2 ranges: Type A1:A10 A5:A15 > Enter. The overlap range of A5:A10 will be selected

Reply | Quote

You can also use the Name Box as a launchpad for your macros/forms. Create several named ranges for some remotely located cells with the name of your macros or forms. In the worksheet module apply a code similar to the following:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
    Case “$AAA$1”
        PrintForm_routine
    Case “$AAA$2”
        ClearSheet_routine
    Case “$AAA$3”
        FormatSheet_routine
    Case “$AAA$4”
        CalcSheet_routine
    Case “$AAA$5”
        MoveData_routine
    Case “$AAA$6”
        DataForm.Show
End Select
End Sub

Note that I added “_routine” to the named range for the name of the macro. Create your macros.

Now select the action (macro) you want to perform from the Named box and the appropriate code will run.

42145-NameBox2

It is best to add a line of code to each routine that will return you back to the main area of your spreadsheet.

HTH,
Maud

Reply | Quote

I don’t know if its new or a permutation of some of the items mentioned by zeddy or maud

1. clicking the Name drop down and then clicking on a name within the list highlights the cells corresponding to the name (I think this is just basic functionality of the Name box).

2. you can also type something like worksheet_name!cell and that will select the cell within the sheet that you typed – for example labs!A20 selects cells A20 from worksheet labs.

Fred

Reply | Quote