Workbook_Open Question

[Guest]

I have the following sub that will shade every other row in
a spreadsheet. 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

 

[Dick Kusleika]

gm

Unless EnableEvents is False or your Workbook_Open sub is the wrong place,
then I think it should work. Put a MsgBox in the workbook open and see if
it's even executed. If not, I'll bet it's in the wrong module. Make sure
it's in the ThisWorkbok module.

Generally, you don't need to Select objects in order to work with them, and
it's inefficient. You might consider changing that sub to

Dim i As Long

With ActiveSheet
For i = 2 to .Rows.Count Step 2
If IsEmpty(.Cells(i,1)) Then
Exit For
Else
.Cells(i,1).EntireRow.Interior.ColorIndex = 15
End If
Next i
End With

 

[JE McGimpsey]

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 sheet and use the technique found here:

http://cpearson.com/excel/banding.htm

 

[Richard Buttrey]

On Sat, 14 Aug 2004 22:28:56 -0600, JE McGimpsey

RB>Unless you don't have the Workbook_Open() code in the ThisWorkbook
RB>module, it should work fine.
RB>
RB>However, instead of running a macro every time, why not select the
RB>entire sheet and use the technique found here:
RB>
RB> http://cpearson.com/excel/banding.htm

That's an extremely interesting method and works very well for
predefined cells. However i've just tried capturing the conditional
formatting key strokes in a macro and other than the first 'select',
nothing else appears.

I'd like to be able to apply this conditional formatting to new rows
introduced by some other VBA code. I could I suppose hold this
formatting in cells in a 'spare' row and copy the formatting each time
a row is introduced, but I'd like to know how to apply it directly
with code.

Any ideas?

Many thanks,

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[JE McGimpsey]

One way:

Public Sub ApplyCF()
With Range("A1:A" & Range("A" & Rows.Count).End( _
xlUp).Row).EntireRow.FormatConditions
.Delete
With .Add(Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0")
.Interior.ColorIndex = 35
End With
End With
End Sub

Adjust your row definition as necessary.

 

[Mike Fogleman]

Using Pearson's formula, the code is:

With Range("D130")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW()-1,3*2)+1<=3"
.FormatConditions(1).Interior.ColorIndex = 35
End With

Mike F

 

[Guest]

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 worksheet opens it corrects for any changes
made in the last edits.
Guy

 

[Guest]

Thanks for the reply. I added the msgbox as you suggest, so now workbook open
looks like:
----------------------------------------------------------------
Private Sub Workbook_Open()
MsgBox ("Workbook open in progress")
ShadeEveryOtherRow
----------------------------------------------------------------
The message box happens, the other code still does not execute. I am assuming
that since the message box is working, then the EnableEvents you mention is not
False.

So can anyone else come up with another reason why this code will not execute
on workbook open?

thanks
gm

 

[Tom Ogilvy]

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?

 

[Guest]

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]

Is the correct sheet the active sheet? Or, is A2 not empty?

Perhaps a blank sheet is the activesheet and thus your code executes, but
does nothing.

Put the message box in the ShadeEveryOtherRow code at the top as the first
executable instruction.

 

[Tom Ogilvy]

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).

 

[Guest]

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 sheet 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

 

[Dick Kusleika]

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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 sheet 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

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

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

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 worksheet 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 sheet and use the technique found here:

http://cpearson.com/excel/banding.htm

 

[b&s]

Just a suggestion:
If you open your file using a macro, put in that macro:
Workbooks.Open "C:\YourFile.xls"
ActiveWorkbook.RunAutoMacros xlAutoOpen

and, in your file call 'ShadeEveryOtherRow' from
Auto_Open() instead Workbook_Open()

 

[Guest]

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

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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 sheet 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

 

[Guest]

Tried it, no change! Thanks anyway. Sooner or later some excel vba guru
will chime in with the explaination.
gm

 

[Dick Kusleika]

gm

If you like, you can email a copy of the workbook to me.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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

 

[Guest]

Thanks, I will use the email link from your blog site.

 

[Dick Kusleika]

gm

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

Sub OpenRebates()

Workbooks.Open "C:\Dick\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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thanks, I will use the email link from your blog site.

gm

If you like, you can email a copy of the workbook to me.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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

Dick 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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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 sheet with these lined

at
the

beginning:
----------------------------------------------------------------
Sub ShadeEveryOtherRow()
MsgBox ("ShadeEveryOtherRow open in progress")
Worksheets("To Be Recieved").Activate
Range("a2").Select

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

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

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 worksheet 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 sheet and use the technique found here:

http://cpearson.com/excel/banding.htm