auto update of conditional macro

@

@Homeonthecouch

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
 
D

Dave Peterson

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?
 
@

@Homeonthecouch

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

Dave Peterson

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

@Homeonthecouch

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
 
D

Dave Peterson

Use the workbook_open suggestion--but change the address.

@Homeonthecouch said:
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
Click to expand...
 
@

@Homeonthecouch

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
 
D

Dave Peterson

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.

@Homeonthecouch said:
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
Click to expand...
 
Top