Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
enter 1 in a cell and it enters static date in next...Have this working in
one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
One way. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target.Value) Then Exit Sub If Target.Column = 21 Or _ Target.Column = 25 Or _ Target.Column = 27 Then Application.EnableEvents = False If Target.Value = 1 Then _ Target.Offset(0, 1).Value = Date Application.EnableEvents = True End If End Sub "HELP ME PLEASE" wrote in message ... enter 1 in a cell and it enters static date in next...Have this working in one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
Thank alot. This works will the date remain static and not change tommorrow?
"Otto Moehrbach" wrote: One way. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target.Value) Then Exit Sub If Target.Column = 21 Or _ Target.Column = 25 Or _ Target.Column = 27 Then Application.EnableEvents = False If Target.Value = 1 Then _ Target.Offset(0, 1).Value = Date Application.EnableEvents = True End If End Sub "HELP ME PLEASE" wrote in message ... enter 1 in a cell and it enters static date in next...Have this working in one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
That code will not work in any worksheet or column using the event
Worksheet_General Private Sub Worksheet_General(ByVal Target As Range) needs to be Private Sub Worksheet_Change(ByVal Target As Range) To work on more columns change the If Not Intersect line to If Not Intersect(Target, Range("U:U,Y:Y, AA:AA")) Is Nothing Then Static means "not changing" among other definitions. Gord Dibben MS Excel MVP On Thu, 28 Feb 2008 11:04:03 -0800, HELP ME PLEASE wrote: enter 1 in a cell and it enters static date in next...Have this working in one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
thanks Gord, I plugged in Ottos script and it worked i will make your
upgrades and let u know how it works "Gord Dibben" wrote: That code will not work in any worksheet or column using the event Worksheet_General Private Sub Worksheet_General(ByVal Target As Range) needs to be Private Sub Worksheet_Change(ByVal Target As Range) To work on more columns change the If Not Intersect line to If Not Intersect(Target, Range("U:U,Y:Y, AA:AA")) Is Nothing Then Static means "not changing" among other definitions. Gord Dibben MS Excel MVP On Thu, 28 Feb 2008 11:04:03 -0800, HELP ME PLEASE wrote: enter 1 in a cell and it enters static date in next...Have this working in one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
Otto's code is fine.
No need to change.......just a different way of doing the same thing. I find my method saves the IF/OR statements and I'm lazy<g Gord On Thu, 28 Feb 2008 16:13:01 -0800, HELP ME PLEASE wrote: thanks Gord, I plugged in Ottos script and it worked i will make your upgrades and let u know how it works "Gord Dibben" wrote: That code will not work in any worksheet or column using the event Worksheet_General Private Sub Worksheet_General(ByVal Target As Range) needs to be Private Sub Worksheet_Change(ByVal Target As Range) To work on more columns change the If Not Intersect line to If Not Intersect(Target, Range("U:U,Y:Y, AA:AA")) Is Nothing Then Static means "not changing" among other definitions. Gord Dibben MS Excel MVP On Thu, 28 Feb 2008 11:04:03 -0800, HELP ME PLEASE wrote: enter 1 in a cell and it enters static date in next...Have this working in one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data enters date in next cell for several columns
The date cell does not contain a formula, just the date. It doesn't change.
Otto "HELP ME PLEASE" wrote in message ... Thank alot. This works will the date remain static and not change tommorrow? "Otto Moehrbach" wrote: One way. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target.Value) Then Exit Sub If Target.Column = 21 Or _ Target.Column = 25 Or _ Target.Column = 27 Then Application.EnableEvents = False If Target.Value = 1 Then _ Target.Offset(0, 1).Value = Date Application.EnableEvents = True End If End Sub "HELP ME PLEASE" wrote in message ... enter 1 in a cell and it enters static date in next...Have this working in one column with the following but need to have it work in columns Y:Y and AA:AA also any one know how? Private Sub Worksheet_General(ByVal Target As Range) If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("U:U")) Is Nothing Then Application.EnableEvents = False If Target.Value = 1 Then Target.Offset(0, 1).Value = Date End If Application.EnableEvents = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting from drop down list enters multiple cell data | Excel Worksheet Functions | |||
Mouse click automatically enters character into cell. | Excel Worksheet Functions | |||
Date enters wrong #'s | New Users to Excel | |||
Master worksheet automatically enters data into sub worksheets | Excel Discussion (Misc queries) | |||
Excel enters date as a text format | Excel Discussion (Misc queries) |