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,D3 6,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


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=375421

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=375421

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.


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=375421

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=375421

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.


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=375421

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 Su

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=37542