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

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

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


wrote:
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

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 he

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



In article , wrote:

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

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

RBUnless you don't have the Workbook_Open() code in the ThisWorkbook
RBmodule, it should work fine.
RB
RBHowever, instead of running a macro every time, why not select the
RBentire sheet and use the technique found he
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
__________________________

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.


In article ,
Richard Buttrey wrote:

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.

Using Pearson's formula, the code is:

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

Mike F
"Richard Buttrey" wrote in
message ...
On Sat, 14 Aug 2004 22:28:56 -0600, JE McGimpsey
wrote:

RBUnless you don't have the Workbook_Open() code in the ThisWorkbook
RBmodule, it should work fine.
RB
RBHowever, instead of running a macro every time, why not select the
RBentire sheet and use the technique found he
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
__________________________

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 he

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

In article , wrote:

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

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

Dick Kusleika wrote:

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

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

wrote:
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

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

wrote in message ...
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 he

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

In article , wrote:

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

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

wrote in message ...
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 he

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

In article , wrote:

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

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.

--
Regards,
Tom Ogilvy

wrote in message ...
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

Dick Kusleika wrote:

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

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

wrote:
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

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

wrote in message ...
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

wrote in message ...
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 he

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

In article , wrote:

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

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

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

wrote in message ...
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

wrote in message ...
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 he

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

In article , wrote:

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

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

wrote in message ...
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

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

wrote in message ...
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

wrote in message
...
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 he

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

In article ,
wrote:

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

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

--
pozdrav!
Berislav


wrote in message
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

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

wrote in message
...
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

wrote in message
...
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 he

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

In article ,
wrote:

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

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

wrote in message ...
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

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

wrote in message ...
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

wrote in message
...
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 he

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

In article ,
wrote:

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

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


b&s wrote:

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

--
pozdrav!
Berislav

wrote in message
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

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

wrote in message
...
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

wrote in message
...
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 he

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

In article ,
wrote:

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

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

wrote in message ...
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

wrote in message ...
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

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

wrote in message
...
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

wrote in message
...
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 he

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

In article ,
wrote:

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

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

Dick Kusleika wrote:

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

wrote in message ...
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

wrote in message ...
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

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

wrote in message
...
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

wrote in message
...
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 he

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

In article ,
wrote:

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

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


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

Dick Kusleika wrote:

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

wrote in message ...
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

wrote in message
...
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

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

wrote in message
...
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

wrote in message
...
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 he

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

In article ,

wrote:

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

Where did you place the OpenRebates sub?
gm

Dick Kusleika wrote:

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

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

Dick Kusleika wrote:

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

wrote in message ...
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

wrote in message
...
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

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

wrote in message
...
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

wrote in message
...
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 he

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

In article ,

wrote:

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

wrote in message ...
Where did you place the OpenRebates sub?
gm


In a standard module in Personal.xls.

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

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


Any more information on this problem? I'm running into a similar
situation with a painfully simple macro. It executes fine with a
shortcut (no SHIFT key in the shortcut so that's not the problem).

The Ctrl + E shortcut works great...but the sub called from
Workbook_Open executes and transposes both columns and rows I'm trying
to copy from one sheet to another.

Shortcut works great...Workbook_Open event does not.

???