Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Keyboard Shortcut to rename Worksheet

This is a discussion on Keyboard Shortcut to rename Worksheet within the Excel Questions forums, part of the Question Forums category; Hi all Is there a keyboard shortcut to select the name of the active worksheet? At the moment I have ...

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Location
    London
    Posts
    62

    Default Keyboard Shortcut to rename Worksheet

    Hi all

    Is there a keyboard shortcut to select the name of the active worksheet? At the moment I have to double click on the [Sheet1] and then retype.

    If there isn't, is there a macro which can select the name of the active worksheet?

    Thanks

    Matt

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,104

    Default Re: Keyboard Shortcut to rename Worksheet

    Here's something I use:

    Option Explicit

    Private Function SheetExists(SheetName As String) As Boolean
        ' Returns TRUE if a sheet exists in the active workbook
        Dim x As Worksheet
            On Error Resume Next
                Set x = ActiveWorkbook.Sheets(SheetName)
                    If Err = 0 Then SheetExists = True _
                    Else SheetExists = False
    End Function

    Sub RenameSheets()
        Dim strNewSheetName As String
        Dim strCurSheetName As String
            
    '        Application.MacroOptions _
                Macro:="PERSONAL.XLS!RenameSheet.RenameSheets", _
                Description:="", _
                ShortcutKey:="q"

            
                strCurSheetName = ActiveSheet.Name
                strNewSheetName = InputBox("Please enter the New Sheet Name", "Rename Sheet", strCurSheetName)
                
                    If SheetExists(strNewSheetName) Then
                        MsgBox "Sheet Name already exists", vbCritical + vbOKOnly, "Sheet Exists"
                        RenameSheets
                    End If
                
                If strNewSheetName = "" Then
                    ActiveSheet.Name = strCurSheetName
                Else: ActiveSheet.Name = strNewSheetName
                End If
                                      
    End Sub



    Note that it includes error handling to check for existing sheet names.

    HTH,
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular
    Join Date
    Oct 2006
    Location
    London
    Posts
    62

    Default Re: Keyboard Shortcut to rename Worksheet

    Love it! Thanks Smitty!

  4. #4
    Board Regular
    Join Date
    Oct 2005
    Posts
    1,553

    Default Re: Keyboard Shortcut to rename Worksheet

    Here's the procedure without a macro:


    ALT+O, H, R

    Harry
    "A real friend is one who walks in when the rest of the world walks out. Don't walk in front of me, I may not follow. Don't walk behind me, I may not lead. Walk beside me and be my friend." - Charles Caleb Colton

  5. #5
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,104

    Default Re: Keyboard Shortcut to rename Worksheet

    I should point out that it doesn't include error handling for invalid sheet names (simply because it's one of my personal procedures, and I know better ). You'll also want to uncomment the shortcut key assignment/add a shortcut of your own.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  6. #6
    Board Regular
    Join Date
    Oct 2005
    Posts
    1,553

    Default Re: Keyboard Shortcut to rename Worksheet

    Smitty,

    Not that I am all that good in Excel or VBA , but didn't the OP ask for a keybord shortcut?
    Quote Originally Posted by Mtyler View Post
    Hi all

    Is there a keyboard shortcut to select the name of the active worksheet? At the moment I have to double click on the [Sheet1] and then retype.

    Matt
    With your Macro solution and my posted keyboard solution, he would still have to type a new name for the sheet.

    Difference is, the capabilites already exists without a macro.

    Tested on Excel2007, don't know if works with 2003 or earlyer.

    Harry
    "A real friend is one who walks in when the rest of the world walks out. Don't walk in front of me, I may not follow. Don't walk behind me, I may not lead. Walk beside me and be my friend." - Charles Caleb Colton

  7. #7
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,104

    Default Re: Keyboard Shortcut to rename Worksheet

    didn't the OP ask for a keybord shortcut?
    Either or actually:

    If there isn't, is there a macro which can select the name of the active worksheet?
    I think the OP meant how to change the name of the sheet without having to use the mouse.

    Quote Originally Posted by hawaiian harry View Post
    With your Macro solution and my posted keyboard solution, he would still have to type a new name for the sheet.

    Difference is, the capabilites already exists without a macro.
    Yup, either way you have to enter a new name. There's no shortcut around that.

    And thanks for revealing a new shortcut to me, I'm always excited to find a new one! Although my code only takes one step.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  8. #8
    Board Regular
    Join Date
    Oct 2005
    Posts
    1,553

    Default Re: Keyboard Shortcut to rename Worksheet

    Okay , I was playing around with your code because I see a potential use for me.

    Working with a new book, I ran your code and it allowed me to change sheet1 to, in my test to test

    Inserted another sheet, ran the code, changed name to test1 in the msgbox, worked fine.

    Inserted another sheet, ran code, changed name to test1 gave me the msg that it already exist. Pressed OK and renamed to test2.

    Run-time error 1004
    Msg Box appeared saying that cannot rename to same name as another sheet.

    Hitting Debug, highlights: Else: ActiveSheet.Name = strNewSheetName

    But it does rename the sheet to test2

    Hit End

    Harry
    "A real friend is one who walks in when the rest of the world walks out. Don't walk in front of me, I may not follow. Don't walk behind me, I may not lead. Walk beside me and be my friend." - Charles Caleb Colton

  9. #9
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,104

    Default Re: Keyboard Shortcut to rename Worksheet

    Quote Originally Posted by hawaiian harry View Post
    Inserted another sheet, ran code, changed name to test1 gave me the msg that it already exist. Pressed OK and renamed to test2.

    Run-time error 1004
    Msg Box appeared saying that cannot rename to same name as another sheet.

    Hitting Debug, highlights: Else: ActiveSheet.Name = strNewSheetName

    But it does rename the sheet to test2
    Sorry Harry, I've never gotten around to fixing that. I just hit Escape, because I suppose I'm used to it, like I said, this is one I wrote for me, not necessarily general consumption.

    One of these days I'll probably work on it... If you want to fix it in the meantime, please feel free. I'm up to my a$$ in financials right now...
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  10. #10
    Board Regular
    Join Date
    Oct 2006
    Location
    London
    Posts
    62

    Default Re: Keyboard Shortcut to rename Worksheet

    Guys

    I appreciated both of your answers. I think I'll go with the macro that Smitty suggested, basically because its only one keyboard shortcut.

    I'm not the biggest fan of the new shortcuts in 2007 where you have to type about 6 keys before you get the item you need. Don't think it's much of a shortcut really!

    Harry - like the old school shortcut though!

    Smitty - have changed the keyboard shortcut too - thanks

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com