Re: Workbook_Open Question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dick Kusleika (dickk_at_paragonUNMUNGEconstructioninc.com)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 10:37:29 -0500

gm

It worked without issue for me. I created this sub to open the workbook

Sub OpenRebates()

Workbooks.Open "C:\***\Ng\08Aug\Rebates.xls"

End Sub

Assigned that to a commandbar button and it all worked fine using XL2000 on
Win98SE (although I don't think the version matters in this case.)

The subs that you have in the ThisWorkbook module should be in standard
modules. Only use the ThisWorkbook module for event procedures (or other
class specific procedures). That's not the problem because it worked for me
as you sent it without modification, but you should move them anyway. 

-- 
*** Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com
<gmnospam@nospam.xyz> wrote in message news:4122C2BF.1AD07B30@ev1.net...
> Thanks, I will use the email link from your blog site.
>
> *** Kusleika wrote:
>
> > gm
> >
> > If you like, you can email a copy of the workbook to me.
> >
> > --
> > *** Kusleika
> > MVP - Excel
> > Excel Blog - Daily Dose of Excel
> > www.dicks-blog.com
> >
> > <gmnospam@nospam.xyz> wrote in message news:41217D50.B52671EA@ev1.net...
> > > I did as you suggested, it doesn't appear to show anything though.
Cell A2
> > is
> > > not empty (it contains 20040415A), but the macro still refuses to
execute
> > on
> > > open from a macro!
> > > thanks for the input
> > > gm
> > >
> > > *** Kusleika wrote:
> > >
> > > > gm
> > > >
> > > > Instead of a MsgBox, put Stop.  That will break the code and you can
use
> > F8
> > > > to go through it line by line.  If, when you get to the If
statement, it
> > > > passes it by, you'll know that A2 is really empty.
> > > >
> > > > --
> > > > *** Kusleika
> > > > MVP - Excel
> > > > Excel Blog - Daily Dose of Excel
> > > > www.dicks-blog.com
> > > >
> > > > <gmnospam@nospam.xyz> wrote in message
news:411F8D6E.C3A4FBAD@ev1.net...
> > > > > The macro is opened via a button, no shift key....
> > > > >
> > > > > I did move the message box to the top of the ShadeEveryOtherRow
code
> > as
> > > > you
> > > > > suggested, and that statement IS being executed, but nothing else.
I
> > > > tried
> > > > > adding some code to the top of ShadeEveryOtherRow that would
guarantee
> > the
> > > > > process starts at the A2 cell in the first *** with these lined
at
> > the
> > > > > beginning:
> > > > > ----------------------------------------------------------------
> > > > > Sub ShadeEveryOtherRow()
> > > > > MsgBox ("ShadeEveryOtherRow open in progress")
> > > > > Worksheets("To Be Recieved").Activate
> > > > > Range("a2").Select
> > > > > ----------------------------------------------------------------
> > > > >
> > > > > This has the same effect, the msgbox works either way, but the
next
> > two
> > > > lines
> > > > > only work if the workbook is opened manually!  What a pain!
> > > > >
> > > > > So the gremlin is still at large.
> > > > > gm
> > > > >
> > > > > Tom Ogilvy wrote:
> > > > >
> > > > > > If you open it using a macro started with a shortcut key
combination
> > > > that
> > > > > > includes the Shift Key, then you need to use one that does not
use
> > the
> > > > shift
> > > > > > key.  the shift key seems to disable macros (similar to when you
> > open a
> > > > > > workbook manually and hold down the shift key).
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > > <gmnospam@nospam.xyz> wrote in message
> > news:411F8404.24DD4E5F@ev1.net...
> > > > > > > I believe you are on to something Tom.  I do open the file
with a
> > > > macro,
> > > > > > and
> > > > > > > if I open it manually, the worksheet_open event runs
correctly.
> > So
> > > > now
> > > > > > what?
> > > > > > > gm
> > > > > > >
> > > > > > > Tom Ogilvy wrote:
> > > > > > >
> > > > > > > > How do you open the workbook with the workbook_open event?
Do
> > you
> > > > open
> > > > > > it
> > > > > > > > with a macro.  Does the macro run if you open it manually?
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > > <gmnospam@nospam.xyz> wrote in message
> > > > news:411F7D97.867DFD36@ev1.net...
> > > > > > > > > The Workbook_Open() code is in the ThisWorkbook module,
and it
> > > > still
> > > > > > > > > doesn't work.  Thanks for the suggestion, but I prefer
running
> > it
> > > > on
> > > > > > > > > open so that each time the work*** opens it corrects for
any
> > > > changes
> > > > > > > > > made in the last edits.
> > > > > > > > > Guy
> > > > > > > > >
> > > > > > > > > JE McGimpsey wrote:
> > > > > > > > >
> > > > > > > > > > Unless you don't have the Workbook_Open() code in the
> > > > ThisWorkbook
> > > > > > > > > > module, it should work fine.
> > > > > > > > > >
> > > > > > > > > > However, instead of running a macro every time, why not
> > select
> > > > the
> > > > > > > > > > entire *** and use the technique found here:
> > > > > > > > > >
> > > > > > > > > >    http://cpearson.com/excel/banding.htm
> > > > > > > > > >
> > > > > > > > > > In article <411EDB1C.5E572635@ev1.net>,
gmnospam@nospam.xyz
> > > > wrote:
> > > > > > > > > >
> > > > > > > > > > > I have the following sub that will shade every other
row
> > in
> > > > > > > > > > > a spread***.  It works fine if I run it manually via
> > > > > > > > > > > alt-F8
> > > > > > > > > >
> > > > > ----------------------------------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > Sub ShadeEveryOtherRow()
> > > > > > > > > > >
> > > > > > > > > > > Range("A2").EntireRow.Select
> > > > > > > > > > >     Do While ActiveCell.Value <> ""
> > > > > > > > > > >         Selection.Interior.ColorIndex = 15
> > > > > > > > > > >         ActiveCell.Offset(2, 0).EntireRow.Select
> > > > > > > > > > >     Loop
> > > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > ----------------------------------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > But if I try to have it run at open, it does nothing.
I
> > > > > > > > > > > have tried placing the code directly in workbook_open,
> > > > > > > > > > > nothing happens.  I have tried calling the sub in
> > > > > > > > > > > workbook_open,
> > > > > > > > > > >
> > > > > > > > > >
> > > > > ----------------------------------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > Private Sub Workbook_Open()
> > > > > > > > > > > ShadeEveryOtherRow
> > > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > ----------------------------------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > and that doesn't work either.  What the heck is going
on
> > > > > > > > > > > here!?
> > > > > > > > > > >
> > > > > > > > > > > Thanks,
> > > > > > > > > > > gm
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
> > >
>
>
>
>
Quantcast