Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modify code
Hi,
I have applied the following code to my worksheet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:A20"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub The code works but does not quite satisfy my requirements. All the cells in the range A1:A20 have dropdown boxes with the choice of B,F,J,N. When B or F is selected the value "Now" sould be entered in the offset cells and if J or N is selected the offset cells shall be cleared. Please assist with this problem. Best regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modify code
Replace:
If IsEmpty(.Value) Then with: If IsEmpty(.Value) Or .Value = "J" OR .Value = "N" Then -- Gary''s Student - gsnu201001 "bigmaas" wrote: Hi, I have applied the following code to my worksheet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:A20"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub The code works but does not quite satisfy my requirements. All the cells in the range A1:A20 have dropdown boxes with the choice of B,F,J,N. When B or F is selected the value "Now" sould be entered in the offset cells and if J or N is selected the offset cells shall be cleared. Please assist with this problem. Best regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modify code
Perfect - many thanks
"Gary''s Student" wrote: Replace: If IsEmpty(.Value) Then with: If IsEmpty(.Value) Or .Value = "J" OR .Value = "N" Then -- Gary''s Student - gsnu201001 "bigmaas" wrote: Hi, I have applied the following code to my worksheet: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A1:A20"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub The code works but does not quite satisfy my requirements. All the cells in the range A1:A20 have dropdown boxes with the choice of B,F,J,N. When B or F is selected the value "Now" sould be entered in the offset cells and if J or N is selected the offset cells shall be cleared. Please assist with this problem. Best regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify Excel (2003) code for Network environment | Setting up and Configuration of Excel | |||
modify a line code | Excel Discussion (Misc queries) | |||
Modify Code | Excel Worksheet Functions | |||
How to modify the code for different type of input? | Excel Worksheet Functions | |||
How to modify the code for different type of input? | Excel Discussion (Misc queries) |