auto update of conditional macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

@Homeonthecouch - 20 Mar 2007 12:39 GMT

Hi again,
I have just noticed that when I open the spreadsheet the cell with the
=today() command automatically updates but the macro that is dependant on
the answer does not update.
I have tried to use refresh F9 but that doesn't work. I can go into the cell
and hit enter and that then refreshes the colour assigned to the day.
Is there a way of doing this when the spreadsheet opens up? Or is that not
advisable?

Andrew

Reply to this Message

Dave Peterson - 20 Mar 2007 13:02 GMT

Are you using the worksheet_calculate event? That event recalced for me when I
opened a workbook with =today() in a cell (xl2003).

If you're not using that event, maybe you could call your macro from the
Workbook_Open event or the Auto_Open procedure?

> Hi again,
> I have just noticed that when I open the spreadsheet the cell with the
[quoted text clipped - 6 lines]
>
> Andrew

Signature

Dave Peterson

Reply to this Message

@Homeonthecouch - 20 Mar 2007 13:19 GMT

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("L22:L23")) Is Nothing Then Exit Sub

With Target
Select Case Weekday(.Value)
Case Is = vbMonday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 7
Case Is = vbTuesday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 44
Case Is = vbWednesday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 6
Case Is = vbThursday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 4
Case Is = vbFriday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 8
Case Is = vbSaturday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 33
Case Is = vbSunday: .Offset(-1, 0).Resize(2,
1).Interior.ColorIndex = 18
End Select
End With

End Sub

So I would assume no?

I am totally inexperienced with VBA and have no real idea what I am doing, I
am learning the methods I find on the net and in these help groups.
Do I need to change the Worksheet_Change to Worksheet_Calculate?
The cell updates perfectly when the workbook is opened but the colour
remains the same as the last time the cell was updated when enter was hit.

Signature

Help and patience is as always appreciated
Thank You

Andrew

> Are you using the worksheet_calculate event? That event recalced for me
> when I
[quoted text clipped - 15 lines]
>>
>> Andrew

Reply to this Message

Dave Peterson - 20 Mar 2007 13:29 GMT

Your routine is checking two cells (L22 and L23) for changes made by the
user--not changes made by calculation.

Do they both contain formulas? Or do you just expect the user to change one of
the cells--and why wouldn't the user just type in a date instead of using the
formula =today().

I guess I don't understand enough to help. (Maybe enough to break what you're
really doing, but not enough to help.)

And without knowing too much, you could always just plop that formula into the
cell when the workbook opens.

Behind thisWorkbook (not behind the worksheet and not in a general module).

Option Explicit
Sub Workbook_Open()
me.worksheets("someworksheetnamehere").range("L22").formula = "=Today()"
end sub

=========
I used L22--I don't have any idea if that's good or bad, though.

> Private Sub Worksheet_Change(ByVal Target As Range)
>
[quoted text clipped - 59 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

@Homeonthecouch - 20 Mar 2007 13:49 GMT

I use the +Today() in cell L23 to automatically update the date and wanted
the day to correspond to a preset colour that I was hoping the conditional
macro would maintain.
I just want the day to change automatically and the colour to follow suite.
No manual input.

Hope this helps you help me :)

Many thanks for the patience, I appreciate I'm way out of my depth and this
is a simple thing but....its a learning curve, well step actually:)

Thanks again.

Andrew

> Your routine is checking two cells (L22 and L23) for changes made by the
> user--not changes made by calculation.
[quoted text clipped - 93 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 20 Mar 2007 13:53 GMT

Use the workbook_open suggestion--but change the address.

> I use the +Today() in cell L23 to automatically update the date and wanted
> the day to correspond to a preset colour that I was hoping the conditional
[quoted text clipped - 112 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

@Homeonthecouch - 20 Mar 2007 15:28 GMT

OOOH I like that little trick :)
Many thanks that could come in rather useful for a whole host of things
It will be a great get around to stop people deleting/altering data in
cells.

Once again, Many Thanks

Andrew

> Use the workbook_open suggestion--but change the address.
>
[quoted text clipped - 128 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 20 Mar 2007 15:51 GMT

If you're gonna do this kind of thing, you may find it better to name the ranges
(insert|Name|define).

Then if you insert or delete rows or columns, you won't have to worry about
changing all the addresses in your code.

> OOOH I like that little trick :)
> Many thanks that could come in rather useful for a whole host of things
[quoted text clipped - 141 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message