Open Workbook to Specific Cell
in [Programming]
|
Prev: How to limit number of Message Boxes on Validation Error?
Next: VBA to export large tables from Excel to SQL Server
From: GEdwards on 26 Apr 2010 12:21 I do not understand why Excel works this way, and would like help please to understand it, and how I can work around it. I am using Excel 2003. I have 2 workbooks. My test workbook (to understand how this is working) has a macro behind a button that calls... Workbooks.Open Filename:="E:\Address and Telephone Book.xls". Now this works fine except that when the "Address and Telephone Book.xls" is opened, the active cell happens to be the last active cell location from the last time the "Address and Telephone Book.xls" was saved. This is very annoying if I am in a workbook that has 1000+ rows and I attempt to open another workbook frim it, and it opens at cell location F1003, but the newly opened workbook has rows 1 thru 50 have actual data, then I have to scroll up to where I want my active cell to be. Can someone please explain why this happens and how I can get my second workbook to open in a specific cell, such as A1 or S35?
From: GEdwards on 26 Apr 2010 13:04 Added info: Within my 2nd WB I have the following Sub upon opening, but it does not appear to be totally working. It does lock all worksheets as required but cell B3 does not become the active cell; Private Sub Workbook_Open() Worksheets("Sheet1").Protect Password:="X9j10K32" Range("B3").Activate Range("B3").Select End Sub "GEdwards" wrote: > I do not understand why Excel works this way, and would like help please to > understand it, and how I can work around it. I am using Excel 2003. > > I have 2 workbooks. My test workbook (to understand how this is working) > has a macro behind a button that calls... Workbooks.Open > Filename:="E:\Address and Telephone Book.xls". > > Now this works fine except that when the "Address and Telephone Book.xls" is > opened, the active cell happens to be the last active cell location from the > last time the "Address and Telephone Book.xls" was saved. This is very > annoying if I am in a workbook that has 1000+ rows and I attempt to open > another workbook frim it, and it opens at cell location F1003, but the newly > opened workbook has rows 1 thru 50 have actual data, then I have to scroll up > to where I want my active cell to be. > > Can someone please explain why this happens and how I can get my second > workbook to open in a specific cell, such as A1 or S35? >
From: Rick Rothstein on 26 Apr 2010 13:16 What happens if you qualify the Range command (you only need one of those as they both do the same thing) with Worksheets("Sheet1") like you did for the Protect statement line? Worksheets("Sheet1").Range("B3").Activate -- Rick (MVP - Excel) "GEdwards" <GEdwards(a)discussions.microsoft.com> wrote in message news:CBFB2485-BF3B-4B14-A840-A88DF8231A9D(a)microsoft.com... > Added info: Within my 2nd WB I have the following Sub upon opening, but it > does not appear to be totally working. It does lock all worksheets as > required but cell B3 does not become the active cell; > > Private Sub Workbook_Open() > Worksheets("Sheet1").Protect Password:="X9j10K32" > > Range("B3").Activate > Range("B3").Select > End Sub > > > "GEdwards" wrote: > >> I do not understand why Excel works this way, and would like help please >> to >> understand it, and how I can work around it. I am using Excel 2003. >> >> I have 2 workbooks. My test workbook (to understand how this is working) >> has a macro behind a button that calls... Workbooks.Open >> Filename:="E:\Address and Telephone Book.xls". >> >> Now this works fine except that when the "Address and Telephone Book.xls" >> is >> opened, the active cell happens to be the last active cell location from >> the >> last time the "Address and Telephone Book.xls" was saved. This is very >> annoying if I am in a workbook that has 1000+ rows and I attempt to open >> another workbook frim it, and it opens at cell location F1003, but the >> newly >> opened workbook has rows 1 thru 50 have actual data, then I have to >> scroll up >> to where I want my active cell to be. >> >> Can someone please explain why this happens and how I can get my second >> workbook to open in a specific cell, such as A1 or S35? >>
From: Gord Dibben on 26 Apr 2010 13:27 Private Sub Workbook_Open() With Worksheets("Sheet1") .Protect Password:="X9j10K32" .EnableSelection = xlNoRestrictions End With Range("B3").Select End Sub Gord Dibben MS Excel MVP On Mon, 26 Apr 2010 10:04:02 -0700, GEdwards <GEdwards(a)discussions.microsoft.com> wrote: >Added info: Within my 2nd WB I have the following Sub upon opening, but it >does not appear to be totally working. It does lock all worksheets as >required but cell B3 does not become the active cell; > >Private Sub Workbook_Open() > Worksheets("Sheet1").Protect Password:="X9j10K32" > > Range("B3").Activate > Range("B3").Select >End Sub > > >"GEdwards" wrote: > >> I do not understand why Excel works this way, and would like help please to >> understand it, and how I can work around it. I am using Excel 2003. >> >> I have 2 workbooks. My test workbook (to understand how this is working) >> has a macro behind a button that calls... Workbooks.Open >> Filename:="E:\Address and Telephone Book.xls". >> >> Now this works fine except that when the "Address and Telephone Book.xls" is >> opened, the active cell happens to be the last active cell location from the >> last time the "Address and Telephone Book.xls" was saved. This is very >> annoying if I am in a workbook that has 1000+ rows and I attempt to open >> another workbook frim it, and it opens at cell location F1003, but the newly >> opened workbook has rows 1 thru 50 have actual data, then I have to scroll up >> to where I want my active cell to be. >> >> Can someone please explain why this happens and how I can get my second >> workbook to open in a specific cell, such as A1 or S35? >>
From: Don Guillett on 26 Apr 2010 14:24
try this in your workbook_open event Application.Goto Sheets("sheet1").Range("s35"), scroll:=True -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "GEdwards" <GEdwards(a)discussions.microsoft.com> wrote in message news:26BF6C17-F6E1-4596-86A0-20E18EFC38FD(a)microsoft.com... >I do not understand why Excel works this way, and would like help please to > understand it, and how I can work around it. I am using Excel 2003. > > I have 2 workbooks. My test workbook (to understand how this is working) > has a macro behind a button that calls... Workbooks.Open > Filename:="E:\Address and Telephone Book.xls". > > Now this works fine except that when the "Address and Telephone Book.xls" > is > opened, the active cell happens to be the last active cell location from > the > last time the "Address and Telephone Book.xls" was saved. This is very > annoying if I am in a workbook that has 1000+ rows and I attempt to open > another workbook frim it, and it opens at cell location F1003, but the > newly > opened workbook has rows 1 thru 50 have actual data, then I have to scroll > up > to where I want my active cell to be. > > Can someone please explain why this happens and how I can get my second > workbook to open in a specific cell, such as A1 or S35? > |