Need to hide date

[Mikeice]

Hi All

Still trying to learn VBA. Sorry.

I have got this code to work but after using the command button wit
code I got from Dave the date is still being displayed.

Here is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.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

Now it works fine when I open the sheet but when I use the Code tha
Dave gave me below:
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

End Sub

So now the first sheet clears but the date and time stay.

How can I clear the date and time after the sheet is cleared and tfre
to the summary sheet?

thx guy

 

[Bob Phillips]

Your change event is recreating it when you update C6. You need to turn
events off

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

On Error GoTo summ_exit
Application.EnableEvents = False
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

summ_exit:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

This portion:

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

Causes the worksheet_change() event to fire. And one of the cells you're
changing is C6--which causes C4 and C3 to be changed to show the date/time.

We can stop the worksheet_Change event from firing:

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

And we don't need to anything special to clean up C3:C4, since those cells are
part of your list.