interior.colorindex causing problems

[Ian]

I cannot work out why the simple code below runs but then the egg
timer shows until you press the ok button.

If you comment out the first line it works fine with no egtimer.
i have tried various other ways of clearing the cells colour but
it is still the same.

i am puzzled.




Private Sub CommandButton1_Click()

Range("colA").Interior.ColorIndex = 0



MsgBox (" blah blah blah"), vbOKOnly

End Sub

 

[Peter T]

Hi puzzled,

try

Range("1:1").Interior.ColorIndex = xlNone

assuming you want to format column 1 with no colour

regards,
Peter T

 

[Tom Ogilvy]

If

assuming you want to format column 1 with no colour

Think you meant

Range("A:A").Interior.ColorIndex = xlNone
or
Columns(1).Interior.ColorIndex = xlNone

rather than

Range("1:1").Interior.ColorIndex = xlNone

which would be Row 1.

 

[Peter T]

Oops - that's exactly what I meant!

Thanks for correcting.

Regards,
Peter T

 

[Ian]

Thanks, but i still get the same:

Private Sub CommandButton1_Click()

Range("A:A").Interior.ColorIndex = xlNone

MsgBox (" blah blah blah"), vbOKOnly

End sub

Cheers,

 

[Guest]

I opened a new workbook. Colored columns 1 and 2 on sheet1 and added a
commandbutton. Put in code like this:

Private Sub CommandButton1_Click()
Columns(1).Interior.ColorIndex = xlNone
MsgBox "Blah Blah Blah", vbOKOnly
End Sub

I went out of design mode and clicked the button.

I got no egg timer (I assume you mean the busy Icon for the mouse).

Try it in a new workbook such as the above. I suspect it has something to
do with the workbook where you are using the code and is unrelated to the
specific code you are using although changing the interior colorindex should
generate no events.

 

[Peter T]

If you are using Excel 97 you need to change the TakeFocusOnClick property
of the commandbutton from True to False. In design mode right click the
button and select properties.

Regards,
Peter T

PS - it's a tip often suggested by Tom, and needed with your code in xl97

 

[Ian]

thanks for your efforts, chaps.

I too have opened virgin workbooks in excel 1997 and 2000 and still
get the eggtimer, except when the mouse hovers over the msgbox when
the pointer changes to the arrow.

thanks,