G'day there again,

Leaving aside the simultaneous updating mentioned in a previous
thread, I've found that the worksheet calendar from John Walkenbach's
book is causing me problems. I've modified it, as per John's suggestion,
to show dates for the entire work area. That is, I've removed the If
function from the array formula and so have 42 dates shown. All else is
the same as the original.

What I've also done is to place code in the Worksheet_Calculate()
event to check the month of the date shown. If the date is in the
current month, then the forecolor is blue & bold. If it's in the
previous or next month, it's not bold and coloured black. Now here's the
puzzling bit:

If I change the date using the scroll bars on top of the calendar,
the cells are updated with the required formatting. Hence the
Worksheet_Calculate() event code is working. However, if I press the
"Current Month" button, the code hangs with a "Runtime error 1004...
Unable to set the Color property of the Font class" message. Likewise,
the same error is generated from trying to "get" the color property in
the immediate window.

Why is that? the code is the same, the only difference that I can
see is that the setting of month & year are done programatically by
calling a general module to change the scrollbars instead of clicking on
them. The Worksheet_Calculate() code is still called, except that now it
can't do anything with the .font properties.

Here is the Worksheet_Calculate() code:

Note that one uses [calarray], the other uses Me.Range...
just to demonstrate that I've used both types of reference. Same problem
with both methods. ( 'calarray' is the range of the 42 "date" cells in
the calendar)

Private Sub Worksheet_Calculate()
Dim X As Integer
Dim Y As Integer
For Y = 1 To 6
For X = 1 To 7
If Month([calarray].Cells(Y, X).Value) < Me.Range
("m").Value Then
With [calarray].Cells(Y, X).Font
.Color = vbBlack
.Bold = False
End With
Else
With Me.Range("calarray").Cells(Y, X).Font
.Color = vbBlue
.Bold = True
End With
End If
Next X
Next Y

End Sub

Hoping for a suggestion,
Ken McLennan
Qld, Australia

What version of Excel? If Excel 97, then change the takefocusonclick
property of the commandbutton to false.

--
Regards,
Tom Ogilvy

"Ken McLennan" wrote in message
.. .
G'day there again,

Leaving aside the simultaneous updating mentioned in a previous
thread, I've found that the worksheet calendar from John Walkenbach's
book is causing me problems. I've modified it, as per John's suggestion,
to show dates for the entire work area. That is, I've removed the If
function from the array formula and so have 42 dates shown. All else is
the same as the original.

What I've also done is to place code in the Worksheet_Calculate()
event to check the month of the date shown. If the date is in the
current month, then the forecolor is blue & bold. If it's in the
previous or next month, it's not bold and coloured black. Now here's the
puzzling bit:

If I change the date using the scroll bars on top of the calendar,
the cells are updated with the required formatting. Hence the
Worksheet_Calculate() event code is working. However, if I press the
"Current Month" button, the code hangs with a "Runtime error 1004...
Unable to set the Color property of the Font class" message. Likewise,
the same error is generated from trying to "get" the color property in
the immediate window.

Why is that? the code is the same, the only difference that I can
see is that the setting of month & year are done programatically by
calling a general module to change the scrollbars instead of clicking on
them. The Worksheet_Calculate() code is still called, except that now it
can't do anything with the .font properties.

Here is the Worksheet_Calculate() code:

Note that one uses [calarray], the other uses Me.Range...
just to demonstrate that I've used both types of reference. Same problem
with both methods. ( 'calarray' is the range of the 42 "date" cells in
the calendar)

Private Sub Worksheet_Calculate()
Dim X As Integer
Dim Y As Integer
For Y = 1 To 6
For X = 1 To 7
If Month([calarray].Cells(Y, X).Value) < Me.Range
("m").Value Then
With [calarray].Cells(Y, X).Font
.Color = vbBlack
.Bold = False
End With
Else
With Me.Range("calarray").Cells(Y, X).Font
.Color = vbBlue
.Bold = True
End With
End If
Next X
Next Y

End Sub

Hoping for a suggestion,
Ken McLennan
Qld, Australia

G'day there Tom,

What version of Excel? If Excel 97, then change the takefocusonclick
property of the commandbutton to false.

Thanks for the suggestion. Unfortunately it's not that simple. I'm
using Win XP Home Edition, and Excel 2002. Also, the commandbutton is
from the Forms Toolbar, not an ActiveX control.

This afternoon I made an interesting discovery. The problem
doesn't manifest until I open up the VBE!! Once I open it, the problem
starts and remains even after I close the editor. If I shut down Excel &
run it all again it works fine, up until I open the editor again. Very
strange.

I don't know how the VBE affects the environment, but I'd like to
find out what's wrong in case something similar occurs in the work
environment. I took the file to work this morning (Windows 2000) and it
does exactly the same thing.

I have absolutely no idea of even where to start looking. Have you
come across anything like that before?

See ya
Ken McLennan
Qld, Australia.

Neither seen nor heard.

I don't see anything in your code that would trigger any concern.

--
Regards,
Tom Ogilvy

"Ken McLennan" wrote in message
.. .
G'day there Tom,

What version of Excel? If Excel 97, then change the takefocusonclick
property of the commandbutton to false.

Thanks for the suggestion. Unfortunately it's not that simple. I'm
using Win XP Home Edition, and Excel 2002. Also, the commandbutton is
from the Forms Toolbar, not an ActiveX control.

This afternoon I made an interesting discovery. The problem
doesn't manifest until I open up the VBE!! Once I open it, the problem
starts and remains even after I close the editor. If I shut down Excel &
run it all again it works fine, up until I open the editor again. Very
strange.

I don't know how the VBE affects the environment, but I'd like to
find out what's wrong in case something similar occurs in the work
environment. I took the file to work this morning (Windows 2000) and it
does exactly the same thing.

I have absolutely no idea of even where to start looking. Have you
come across anything like that before?

See ya
Ken McLennan
Qld, Australia.

G'day there Tom,

Neither seen nor heard.

At least it's nice to know that I'm original =)

I don't see anything in your code that would trigger any concern.

Thanks very much for looking.

I guess I'll just run with it as is, unless I can get the IT
people to install the MS Calendar Control to the network. As it's free,
I might stand a chance.

Of my potential users, about 6,000 of them, I'd guess that there
would probably be only 200 or so that would use the VBE anyway so it's
not really an issue unless something else can set the code going
haywire.

Thanks for your time,
I appreciate your efforts
--
Ken McLennan
Qld, Australia

G'day there One & All,

Just in case anyone is following this thread, I resolved the issue
by replacing all the controls with their OLE versions. There was just a
minor change to be made to my code as the OLE ScrollBars don't have a
..Value property. They have a .LinkedCell instead, which also doesn't
have a value property. I had to save the value to the sheet by writing
to the LinkedCell as a range. An easy fix and no problem from then on.

Thanks again, Tom.

See ya
Ken McLennan