date problem

P

puiuluipui

Hi, i need the date and day in C1, but only when i write something in C8. And
when i close the workbook, and open it again, the date to remain the same.

Ex:
Today is MONDAY 03.11.2008

When i write something in C8, "MONDAY 03.11.2008" to appear in C1.
If i close the workbook, i need the date to be saved.
If i open the workbook after 3 days, C1 to show me the same "MONDAY
03.11.2008"
Can it be done?
Thank you in advance.
 
M

Mike H

Hi,

Right click your sheet tab, view code and paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$C$8" Then
Target.Offset(-7) = Format(Date, " dddd dd mm yyyy")
End If
End Sub

Mike
 
S

Stefi

This event sub does the job:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C8" Then _
Range("C1").Value = "Today is " & Format(Date, "dddd dd.mm.yyyy")
End Sub

Post if you need help to install it!

Regards,
Stefi


„puiuluipui†ezt írta:
 
P

puiuluipui

THANKS MIKE, STEFI, IT'S WORKING. BOTH OF THEM.

I have one more problem. I need to print the worksheet but i need the macro
to autoajust the print area. I need to print 6 columns from A3 till the last
written row.

I don't want to set a print area, i just want to automatically print the
worksheet. Sometimes the worksheet has 10 rows and sometimes 100 rows.
Can you help me?
THANKS ALLOT
 
S

Stefi

Try to insert this in the workbook code window (it supposes that cell in the
last written row in column A is not empty):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "$A$3:$F$" & lastrow
End Sub

Regards,
Stefi

„puiuluipui†ezt írta:
 
S

Stefi

You are welcome! Thanks for the feedback!
Stefi

„puiuluipui†ezt írta:
 
P

puiuluipui

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C8" Then _
Range("C1").Value = "Today is " & Format(Date, "dddd dd.mm.yyyy")
End Sub

Stefi, can the day be with upper case? (MONDAY, FRIDAY...ETC?)




THANKS
"Stefi" a scris:
 
S

Stefi

Range("C1").Value = "Today is " & Ucase(Format(Date, "dddd dd.mm.yyyy"))
Stefi


„puiuluipui†ezt írta:
 
P

puiuluipui

IT'S WORKING.
BUT, I PROMISS, IT'S THE LAST QUESTION

If i delete the data from C8 the formula its still in C1. I use this
workbook every week, and at the begining of each week i make a copy and clear
the data from it.
Can the datas from the C1 dissapear when i clear the C8 cell?
Thanks allot.

"Stefi" a scris:
 
S

Stefi

Range("C1").Value = IIf(IsEmpty(Target), "", "Today is " &
UCase(Format(Date, "dddd dd.mm.yyyy")))
Stefi

„puiuluipui†ezt írta:
 
P

puiuluipui

Thanks!
Works great!


"Stefi" a scris:
Range("C1").Value = IIf(IsEmpty(Target), "", "Today is " &
UCase(Format(Date, "dddd dd.mm.yyyy")))
Stefi

„puiuluipui†ezt írta:
Click to expand...
 
Top