Getting random cells entering date please help

[Mikeice]

I have a survey form that I am using but when I click on the command button to clear form Command Button 1
THe form clears but then the date is put in 8 different cells.

DOn't understand why - PLease help.


Private Sub CommandButton1_Click()
Range("D3,C5:C7").Select
Range("C5").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D15:D19").Select
Range("D15").Activate
ActiveWindow.SmallScroll Down:=10
Range("D3,C5:C7,D15:D19,D22:D24").Select
Range("D22").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D15:D19,D22:D24,D27:D29").Select
Range("D27").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D15:D19,D22:D24,D27:D29,D32:D33").Select
Range("D32").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D15:D19,D22:D24,D27:D29,D32:D33,D36,C40").Select
Range("C40").Activate
Selection.ClearContents
Range("C3,C4, c5").ClearContents
Range("C5").Select

End Sub

Private Sub CommandButton3_Click()

Dim Summary As Worksheet
Dim myFromAddr As Variant
Dim myToRow As Variant
Dim iCtr As Long
Dim LastCol As Range
Dim NextColNum As Long

myToRow = Array(1, 2, 3, 4, 5, 6, _
8, 9, 10, 11, 12, _
14, 15, 16, 18, 19, _
20, 22, 23, 25, 27)

myFromAddr = Array("C3", "C4", "C5", "C6", "c7", "D3", _
"D15", "D16", "D17", "D18", "D19", _
"D22", "D23", "D24", "D27", "D28", _
"D29", "D32", "D33", "D36", "c40")

If UBound(myToRow) <> UBound(myFromAddr) Then
MsgBox "Design error--not same number of cells!"
Exit Sub
End If

If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
MsgBox "Please fill in cell: " & myFromAddr(LBound(myFromAddr))
Exit Sub
End If

Set Summary = Worksheets("Summary")

With Summary
Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column
Else
NextColNum = LastCol.Column + 1
End If

For iCtr = LBound(myToRow) To UBound(myToRow)
.Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value
Me.Range(myFromAddr(iCtr)).ClearContents
Next iCtr


End With
Range("C3,C4, c5").ClearContents
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = True
Application.EnableEvents = False
If Not Intersect(Target, Range("C6")) Is Nothing Then
With Target
.Offset(-2, 0).Value = Format(Time, "hh:mm")
.Offset(-3, 0).Value = Format(Date, "dd/mm/yyyy")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[anilsolipuram]

that because of the code in worksheet_change, it makes cell value time, date whe n you clear cells (change cell vallue). what exactly are you trying to do with this code.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = True
Application.EnableEvents = False
If Not Intersect(Target, Range("C6")) Is Nothing Then
With Target
.Offset(-2, 0).Value = Format(Time, "hh:mm")
.Offset(-3, 0).Value = Format(Date, "dd/mm/yyyy")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[Mikeice]

HI THx for getting back to me.

I am trying to have the system time and date entered when entering in cell c6.
then when the information is saved and the sheet is cleared. I need the date and time to update to the new system time/date.

The worksheet is a survey form that outbound callers are using fo a survey and the time needs to be recorded each time they start a new survey.

[anilsolipuram]

Which cell do you want the date and time to be entered.

what do you mean by trying to have the system time and date entered when entering in cell c6

[Mikeice]

Hi there
I want the date and time to appear in c3 (date) c4 (time) when something is entered in c6.

What I'm finding is when saving/clearing the form either the date doesn't work or the date is spread randomly through the worksheet.

[anilsolipuram]

corrected code below.

try it and let me know.


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = True
Application.EnableEvents = False
If Not Intersect(Target, Range("C6")) Is Nothing Then

range("c4").Value = Format(Time, "hh:mm")
range("c3").Value = Format(Date, "dd/mm/yyyy")
End If
ws_exit:
Application.EnableEvents = True
End Sub

[Tom Ogilvy]

I believe somewhere along the way, you lost the periods in front of Offset

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$C$6" Then
With Target
.Offset(-2, 0).Value = Format(Time, "hh:mm")
.Offset(-3, 0).Value = Format(Date, "dd/mm/yyyy")
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


--
Regards,
Tom Ogilvy

"anilsolipuram" <[email protected]>
wrote in message
news:[email protected]...
>
> corrected code below.
>
> try it and let me know.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = True
> Application.EnableEvents = False
> If Target.Address = "$C$6" Then
> With Target
> Offset(-2, 0).Value = Format(Time, "hh:mm")
> Offset(-3, 0).Value = Format(Date, "dd/mm/yyyy")
> End With
> End If
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile:
http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=375421
>