Need to hide date

  • Thread starter Thread starter Mikeice
  • Start date Start date
M

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
 
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
 
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.
 
Back
Top