Keyboard Shortcut to rename Worksheet

Mtyler

Board Regular
Joined
Oct 13, 2006
Messages
62
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Here's something I use:

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> SheetExists(SheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#007F00">' Returns TRUE if a sheet exists in the active workbook</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> x = ActiveWorkbook.Sheets(SheetName)<br>                <SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN> SheetExists = True _<br>                <SPAN style="color:#00007F">Else</SPAN> SheetExists = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> RenameSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> strNewSheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strCurSheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>        <br><SPAN style="color:#007F00">'        Application.MacroOptions _<br>            Macro:="PERSONAL.XLS!RenameSheet.RenameSheets", _<br>            Description:="", _<br>            ShortcutKey:="q"</SPAN><br>        <br>            strCurSheetName = ActiveSheet.Name<br>            strNewSheetName = InputBox("Please enter the New Sheet Name", "Rename Sheet", strCurSheetName)<br>            <br>                <SPAN style="color:#00007F">If</SPAN> SheetExists(strNewSheetName) <SPAN style="color:#00007F">Then</SPAN><br>                    MsgBox "Sheet Name already exists", vbCritical + vbOKOnly, "Sheet Exists"<br>                    RenameSheets<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>            <SPAN style="color:#00007F">If</SPAN> strNewSheetName = "" <SPAN style="color:#00007F">Then</SPAN><br>                ActiveSheet.Name = strCurSheetName<br>            Else: ActiveSheet.Name = strNewSheetName<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                                   <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

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

HTH,
 
Upvote 0
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.
 
Upvote 0
Smitty,

Not that I am all that good in Excel or VBA , but didn't the OP ask for a keybord shortcut?
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
 
Upvote 0
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.

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. ;)
 
Upvote 0
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
 
Upvote 0
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...:rolleyes: If you want to fix it in the meantime, please feel free. I'm up to my a$$ in financials right now...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top