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 15:07
Rick and Gord,
Thanks for your feedback; my issue is now solved. I had the correct coding
as required however after debugging a larger macro that actually does the
OPEN, I later saw that I had a "Range(actvCell).select" (where actvCell was
defined as the active cell in the issuing macro). With that, it was of
course overriding my "Private Sub Workbook_Open()" routine.

Frustrated, but happy!


"Rick Rothstein" wrote:

> 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?
> >>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: How to limit number of Message Boxes on Validation Error?
Next: VBA to export large tables from Excel to SQL Server