none
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
    
    • Edited by ram_vcc Tuesday, September 06, 2011 2:36 PM
    Tuesday, September 06, 2011 2:29 PM

Answers

  • 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.
    Wednesday, September 07, 2011 6:47 AM
    Moderator

All replies

  • 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

    Tuesday, September 06, 2011 8:26 PM
    Moderator
  • 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

    Wednesday, September 07, 2011 4:31 AM
  • 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

     

     

    Wednesday, September 07, 2011 6:36 AM
    Answerer
  • 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.
    Wednesday, September 07, 2011 6:47 AM
    Moderator
  • @Sid:

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

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Wednesday, September 07, 2011 4:38 PM
  • @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.
    Wednesday, September 07, 2011 4:48 PM
    Moderator
  • 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
    Wednesday, September 07, 2011 4:55 PM
  • @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

    Wednesday, September 07, 2011 5:16 PM