Results 1 to 4 of 4
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Using the Excel Name box

    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
    Last edited by zeddy; 2015-09-20 at 13:38.

  2. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    MOSTATE (2015-09-22),RetiredGeek (2015-09-20)

  3. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Using the Name Box as a Launchpad

    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.

    NameBox2.png

    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

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    X_LD (2015-10-19)

  6. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •