| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
| Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
| Date problem | Excel Discussion (Misc queries) | |||
| date problem | Excel Worksheet Functions | |||
| Date Problem | New Users to Excel | |||
Linear Mode
