Put "Hidden" Message in Cell A1

[R. Lehr]

I wanted to put a "hidden" message in cell A1 of Sheet 2 if the current date
is greater than a given date. I tried: in cell E65536 (using white font):

=IF(NOW()>4/14/04,(A1="Happy Birthday"),(A1=""))
or
=IF(NOW()>38091,(A1="Happy Birthday"),(A1=""))

But neither worked [it would not put either condition in A1; but would work
in the formula cell]. What am I doing wrong?

Thanks in advance.

RL ~ 4/16/04

 

[JE McGimpsey]

One way:

Worksheet functions can only return values to their calling cells, they
can't affect other cells' values.

A1: =IF(TODAY()=DATEVALUE(2004,4,14),"Happy Birthday","")

 

[RK]

In any open workbook, right-click the Excel logo at the top left (next to
'File' menu), select 'View Code' and enter the following code. The "Happy
Birthday" message will be shown in cell A1 on the selected date.


Private Sub Workbook_Open()
Dim Today As Date
Const BDay As Date = #4/17/2004#
Today = Int(Now)
If Today = BDay Then
With Sheets("Sheet1").Range("A1")
.Value = "Happy Birthday!"
.Font.Bold = True
.Font.ColorIndex = 3
.Font.Size = 36
End With
End If
End Sub


Warning : This code will overwrite whatever is in cell A1.

 

[Bazza]

As JE points out ....
Sample for consideration....in Sheet 2 (A1 ?) insert the following.

=IF(TODAY()>=Sheet1!A1,"Sheet current","Sheet out of date")

 

[R. Lehr]

RK . . .

Thanks - your code works great.

JE and Bazza dispelled a myth I had: I thought I could have an "if" formula
change the contents of another cell if the condition is true [or false]; so,
I tried another approach:

I put in C3: =IF(NOW()>38095,"Happy Birthday Debbie","")

Then I did a conditional format to change the font to Bold and Dark Blue.
So only when her birthday is here will the words appear on what otherwise
looks like a completely empty sheet. On any date before her birthday she
will only see an empty worksheet. She can select all - but still not see
anything. [If she toggles to show formulas - well then she can - but I was
counting on her not to do that].

Now that I see your method - it is so much better.

By the way: the sheet reference on my first post was because the worksheet
had two sheets: Sheet1 (a work related sheet); and Sheet2 - renamed "Hidden
Message" - (for the Happy Birthday message).


Thanks again.

RL

In any open workbook, right-click the Excel logo at the top left (next to
'File' menu), select 'View Code' and enter the following code. The "Happy
Birthday" message will be shown in cell A1 on the selected date.


Private Sub Workbook_Open()
Dim Today As Date
Const BDay As Date = #4/17/2004#
Today = Int(Now)
If Today = BDay Then
With Sheets("Sheet1").Range("A1")
.Value = "Happy Birthday!"
.Font.Bold = True
.Font.ColorIndex = 3
.Font.Size = 36
End With
End If
End Sub


Warning : This code will overwrite whatever is in cell A1.

I wanted to put a "hidden" message in cell A1 of Sheet 2 if the current date
is greater than a given date. I tried: in cell E65536 (using white font):

=IF(NOW()>4/14/04,(A1="Happy Birthday"),(A1=""))
or
=IF(NOW()>38091,(A1="Happy Birthday"),(A1=""))

But neither worked [it would not put either condition in A1; but would work
in the formula cell]. What am I doing wrong?

Thanks in advance.

RL ~ 4/16/04

 

[RK]

You're welcome.

Unfortunately, the macro will only work if the user enables macros on
opening the file. You have two options to by-pass this:-
1. Set macro security to 'Low' (not recommended!)
2. Digitally sign the macro so that it will run without any promts (only on
the same pc the macro was created).

RK . . .

Thanks - your code works great.

JE and Bazza dispelled a myth I had: I thought I could have an "if" formula
change the contents of another cell if the condition is true [or false]; so,
I tried another approach:

I put in C3: =IF(NOW()>38095,"Happy Birthday Debbie","")

Then I did a conditional format to change the font to Bold and Dark Blue.
So only when her birthday is here will the words appear on what otherwise
looks like a completely empty sheet. On any date before her birthday she
will only see an empty worksheet. She can select all - but still not see
anything. [If she toggles to show formulas - well then she can - but I was
counting on her not to do that].

Now that I see your method - it is so much better.

By the way: the sheet reference on my first post was because the worksheet
had two sheets: Sheet1 (a work related sheet); and Sheet2 - renamed "Hidden
Message" - (for the Happy Birthday message).


Thanks again.

RL

In any open workbook, right-click the Excel logo at the top left (next to
'File' menu), select 'View Code' and enter the following code. The "Happy
Birthday" message will be shown in cell A1 on the selected date.


Private Sub Workbook_Open()
Dim Today As Date
Const BDay As Date = #4/17/2004#
Today = Int(Now)
If Today = BDay Then
With Sheets("Sheet1").Range("A1")
.Value = "Happy Birthday!"
.Font.Bold = True
.Font.ColorIndex = 3
.Font.Size = 36
End With
End If
End Sub


Warning : This code will overwrite whatever is in cell A1.

I wanted to put a "hidden" message in cell A1 of Sheet 2 if the

current
date

is greater than a given date. I tried: in cell E65536 (using white font):

=IF(NOW()>4/14/04,(A1="Happy Birthday"),(A1=""))
or
=IF(NOW()>38091,(A1="Happy Birthday"),(A1=""))

But neither worked [it would not put either condition in A1; but would work
in the formula cell]. What am I doing wrong?

Thanks in advance.

RL ~ 4/16/04