Microsoft

Office challenge: What's the easiest way to select a specific cell when you return to a specific sheet?

In this week's challenge, help a user preserve the cursor location in a sheet whenever he/she returns to it.

One of your users wants to return to the same cell when returning to a sheet, regardless of the cursor's location when they left that sheet. For example, when the user moves to Sheet1, she wants the cursor to always select cell C3. How would you satisfy this request? There's more than one way to accomplish this - let's opt for the easiest method, one you could show the user to implement herself, if possible.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

22 comments
foreigner
foreigner

1) From the Menu Bar (top), under the View menu (ALT-V), enable the Formula Bar. 2) Select the cell(s) desired. 3) From the Menu Bar, under the Insert menu (ALT-I), choose Name / Define / {Add name (no spaces) to text box. See that the name Refers to cells} 4) Choose the name just defined from the Name Box on the left of the Formula Bar displaying the list of names with the down triangle Since the Name box is a Combo Box, one may enter cells' coordinates (ex. E15; B52:Q69) into it then press Enter. The cell(s) addressed by those coordinates will be then selected. This is a way in Excel 2000. Natually later versions would have better ways!

Mark.Mathews
Mark.Mathews

He means hit the F5 key to bring up the Go To dialog box. Then type C3 and hit enter to go to cell C3. That's not exactly wht the challenge was though.

wwgorman
wwgorman

This lists the current cell selected. Simply type in "C3" "Enter." The method described be rsmail_genesco above works too (be careful if you use a portable computer with an external keyboard as on my external keyboard this now becomes "F3" "C3" "Enter." As I work all over a spreadsheet I have a Post-It Pad with the important cells written down when I am doing a lot of entries and just type in the cell I want to go to in the "active cell indicator" and hit enter.

carl2942
carl2942

How can i create a macro that will delete a value when another cell =TRUE

sbsinc
sbsinc

F5 SheetName!C5 Enter

pctrain
pctrain

Using the GoTo key (F5) or (Control+G) and then typing in the cell address and then pressing Enter is one way. The other way is to ensure your cursor is in the cell you want to remain in and then Save the file. It will always open with that cell being the active cell.

patobrien.3g
patobrien.3g

In the "ThisWorkbook" object for the sheet activate event.... then you can define any cell to be the starting point by creating a named range on each sheet ... in this example named "StartHere". Private Sub Workbook_SheetActivate(ByVal Sh As Object) On Error GoTo ErrorHandler Sh.Range("StartHere").Activate ErrorHandler: End Sub In Excel 2010 you'll have to make the scope of the named range to be the worksheet.

Roger Spencelayh
Roger Spencelayh

.. not so simple but doesn't need the user to do anything. On the Sheet's Activate event: Private Sub Worksheet_Activate() Range("C3").Select End Sub

RRB
RRB

Assign a name to the desired cell. From any sheet open the Names Box and select that name. Names are easier to remember that cell locations after a while. Besides you don't even have to remember it, as you can select it from a list.

DUBLINAL
DUBLINAL

Private Sub Worksheet_Activate() ' This Sub-Macro runs on Actvating the Sheet ' Note Macros need to be enabled on entering the worksheet Range("C3").Select ' Selects the specified cell using the Range Method End Sub ' Finished with VBA and returned back to Excel

gsteel
gsteel

There doesn't seem to be a link to get to the solutions. Just a page of introduction.

derwil
derwil

The user could create a hyperlink on other pages that bring her back to the page and cell required. She would either right click the cell and select 'Hyperlink' or press ctrl+K to open the Edit Hyperlink dialogue box. Choose 'Place in This Document' select the required sheet. In the 'Type the cell reference' insert C3. Whenever the link is selected it will then take her back to the sheet and cell required.

Duski
Duski

Private Sub Worksheet_Activate() Range("C3").Select End Sub

RU7
RU7

Right click the tab Select "View Code" Then, copy and paste this code into the VBA window: Private Sub Worksheet_Activate() Range("c3").Select End Sub Close the VBA window Test by selecting another cell, then switching to another tab and back. If it works, save the file. Make sure to enable macros if asked.

Rudi-S
Rudi-S

Right click the sheet tab Add the code below to the sheet activate event: Private Sub Worksheet_Activate() Range("C3").Select End Sub Thats it. Each time the user selects Sheet1, the cursor goes then immedately.

emilefyon
emilefyon

Just add the following code in the worksheet. Private Sub Worksheet_Activate() Range("c3").Select End Sub

RU7
RU7

Add a list of all the sheets in the workbook with a possibly different cell to select when any given sheet is activated and have it all in one place for easy management. Private Sub Workbook_SheetActivate(ByVal Sh As Object) On Error GoTo ErrorHandler Selected_Cell=Switch(sh.name="Sheet 1", "C3", _ sh.name="Sheet 2", "D5", _ sh.name="Sheet 3", "G2") Sh.Range(Selected_Cell).Activate ErrorHandler: End Sub For easier management you could even add the sheet names and target cells as constants up front and refer to the constants in the procedure. This is not so important here but as the quantity of code grows, so do the benefits.

JaneHawkins
JaneHawkins

To be MORE specific The F5 key (Go To dialogue box) Type C3 at the cursor position Enter to close the box and Go To cell C3

RU7
RU7

to automatically select a given cell whenever a given sheet is activated. In other words, whenever sheet 1's tab is clicked, the sheet opens with cell C3 selected, no matter what cell was selected in the previous sheet or what cell was selected when sheet 1 was last active.

emilefyon
emilefyon

I think the word "Specific" is important here... Anyway, not a really big deal !

ssharkins
ssharkins

By specific, I mean the same cell each time. I'm not sure I understand F5, C3, Enter.