ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   date problem (https://www.excelbanter.com/excel-discussion-misc-queries/208754-date-problem.html)

puiuluipui
date problem
 
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.

Mike H
date problem
 
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

"puiuluipui" wrote:

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.

Stefi
date problem
 
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:

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.

puiuluipui
date problem
 
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

Stefi
date problem
 
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:

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

puiuluipui
date problem
 
IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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

Stefi
date problem
 
You are welcome! Thanks for the feedback!
Stefi

€˛puiuluipui€¯ ezt Ć*rta:

IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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

puiuluipui
date problem
 
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:

You are welcome! Thanks for the feedback!
Stefi

€˛puiuluipui€¯ ezt Ć*rta:

IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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

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


€˛puiuluipui€¯ ezt Ć*rta:

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:

You are welcome! Thanks for the feedback!
Stefi

€˛puiuluipui€¯ ezt Ć*rta:

IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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

puiuluipui
date problem
 
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:

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


€˛puiuluipui€¯ ezt Ć*rta:

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:

You are welcome! Thanks for the feedback!
Stefi

€˛puiuluipui€¯ ezt Ć*rta:

IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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

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

€˛puiuluipui€¯ ezt Ć*rta:

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:

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


€˛puiuluipui€¯ ezt Ć*rta:

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:

You are welcome! Thanks for the feedback!
Stefi

€˛puiuluipui€¯ ezt Ć*rta:

IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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

puiuluipui
date problem
 
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:

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:

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


€˛puiuluipui€¯ ezt Ć*rta:

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:

You are welcome! Thanks for the feedback!
Stefi

€˛puiuluipui€¯ ezt Ć*rta:

IT'S WORKING GREAT!

Thanks allot.

"Stefi" a scris:

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:

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


All times are GMT +1. The time now is 10:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com