PDA

View Full Version : [SOLVED] Cant delete contents of a cell ... VBA issue



Southernwave
10-31-2016, 05:18 AM
This should be simple.....

I have a sheet that ..when a entry is made in column B, the date is entered in column H.. all that works well until I found that if I remove the data in column B-G, the date in H stays.. and everytime I delete it the macro below runs and puts the date back... and I don't really want it to work that way.

Here is the code I am currently using


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect
If Target(1, 1).Row > 1 Then
With Cells(Target(1, 1).Row, "h")
.Value = Date
End With
End If
ActiveSheet.Protect
End Sub


Any suggestions ?

Cheers

Andrew

mana
10-31-2016, 06:11 AM
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim c As Range

Set r = Intersect(Target, Columns("B"))
If r Is Nothing Then Exit Sub

ActiveSheet.Unprotect
Application.EnableEvents = False
For Each c In r
If c.Row > 1 Then
With c.EntireRow
If WorksheetFunction.CountA(.Range("B1:G1")) > 0 Then
.Range("H1").Value = Date
Else
.Range("H1").ClearContents
End If
End With
End If
Next
Application.EnableEvents = True
ActiveSheet.Protect
'
End Sub

Southernwave
10-31-2016, 10:57 PM
Hi Mana and thank you for the quick reply. I have checked it on the sheet .. and also in a new book but it doesn't seems to either add a date or remove when I empty the cell in column B.

I will keep looking ... but thank you again

snb
11-01-2016, 01:14 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 and target.column=2 Then
ActiveSheet.Unprotect
for each cl in target
cl.offset(,6)= iif(cl.value="","",date)
next
ActiveSheet.Protect
End If
End Sub

Bob Phillips
11-01-2016, 02:53 AM
If you protect the sheet, as you do in your code, you can't make a change to any cells in that sheet, so the code never fires. You need to unlock cells that you can modify.

Southernwave
11-01-2016, 01:46 PM
SNB... That works .. many thanks