M
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,D1519").Select
Range("D15").Activate
ActiveWindow.SmallScroll Down:=10
Range("D3,C5:C7,D1519,D2224").Select
Range("D22").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D1519,D2224,D2729").Select
Range("D27").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D1519,D2224,D2729,D3233").Select
Range("D32").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D1519,D2224,D2729,D3233,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
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,D1519").Select
Range("D15").Activate
ActiveWindow.SmallScroll Down:=10
Range("D3,C5:C7,D1519,D2224").Select
Range("D22").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D1519,D2224,D2729").Select
Range("D27").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D1519,D2224,D2729,D3233").Select
Range("D32").Activate
ActiveWindow.SmallScroll Down:=5
Range("D3,C5:C7,D1519,D2224,D2729,D3233,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