Setting the Focus to a Cell in Excel

Question

How do I set focus to a Cell. For Example I keep the cell G7 active and Close the Excel sheet. While I am opening it should set focus on D4. How is this possible

I have tried the Following but none works.

 

ActiveSheet.Cells(5, 4).Select
				

-or-

ActiveSheet.Range("D5").Select

Answer 1

When saved a Workbook retains the last activecell on each sheet and the sheet that was active when saved (or last active if the workbook is saved while not active). IOW, when the workbook is reopened the active cell, or focus as you put it, will be on the activecell as saved.

With the information you have given it's hard to suggest why that is not happening for you.

Peter Thornton

Answer 2

ram_vcc:

If you create an Auto_Open subroutine, it will execute when you open your workbook:

Make sure you are setting the focus on the correct sheet.  To be sure, you could create your Auto_Open as follows:

Option Explicit

Public Sub Auto_Open()
Sheets("Sheet1").Range("D5").Select
End Sub

Try inserting the above code into your application.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 3

you can try any combination of three or individually.

1. ActiveSheet.Range("D5").activate

2. Activewindow.ScrollRow=Range("d5").row
    Activewindow.ScrollColumn=Range("d5").Column

 

3.Application.Goto Reference:=Range("D5"),Scroll:=True

 

 

Answer 4

How do I set focus to a Cell. For Example I keep the cell G7 active and Close the Excel sheet. While I am opening it should set focus on D4. How is this possible

I have tried the Following but none works.

 

ActiveSheet.Cells(5, 4).Select
				

-or-

ActiveSheet.Range("D5").Select

  Ram, it depends where are you calling that code from :)

Place the code in the workbook open event :)

 

Option Explicit

Private Sub Workbook_Open()
    ActiveSheet.Range("D5").Select '<~~ Change it to D4 if you want D4 to become Active
End Sub

See the image attached.

Also ensure that the macros are enabled :)

 

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 5

@Sid:

Isn't the Auto_Open really the same as the Workbook_Open?

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Answer 6

@Sid:

Isn't the Auto_Open really the same as the Workbook_Open?

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com

Umm, not exactly :)

1) Workbook_Open will take precedence over Auto_Open.
2) Auto_Open is only provided for backward compatibility and will not fire when a workbook is opened via code. Whereas Workbook_Open will.
3) Similarly, it is the same for Workbook_BeforeClose and Auto_close.

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 7

In nearly all respects, Auto_Open is the same as Workbook_Open. Auto_Open runs after Workbook_Activate which runs after Workbook_Open. Auto_Open will not run if you open the workbook via VBA code (e.g,. Workbooks.Open filename:="C:\Book1.xlsm"). If you need to run an Auto macro when opening a workbook via code, use ThisWorkbook.RunAutoMacros xlAutoOpen.

---

Chip Pearson
Excel MVP (1998 - 2011)
Pearson Software Consulting, LLC
www.cpearson.com

Answer 8

@Sid and Chip:

Thanks gentlemen for the explanation of the differences.  I will add that to my knowledgebase.

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com