desperate and urgent
 

i need a macro that will make a cell value blank when another cell is changed.

i cant use a formula because the cell that needs to be blank has data
validation on it and so takes entry from a list

short example of what i want is:

if cell D9 is changed in any way then cells D10,D11 and D12 will be cleared
( = "")

please help

desperate and urgent
 

Not sure exactly what you want but this does it as best I can understand

Dim dTest As String
Dim myChange As Boolean

Private Sub Worksheet_Activate()
dTest = Range("D9").Value
myChange = False

End Sub

Private Sub Worksheet_Calculate()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


If myChange = False Then
If Range("D9").Value < dTest Then
Range("D10") = ""
Range("D11") = ""
Range("D12") = ""
myChange = True
End If
End If

End Sub

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"max power" wrote:

i need a macro that will make a cell value blank when another cell is changed.

i cant use a formula because the cell that needs to be blank has data
validation on it and so takes entry from a list

short example of what i want is:

if cell D9 is changed in any way then cells D10,D11 and D12 will be cleared
( = "")

please help

desperate and urgent
 

Please endeavor to use a more descriptive subject line. All requests here
are considered to be desperate and urgent and this can anger some. See if
this helps

right click sheet tabview codeinsert thisSAVE workbook

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$D$9" Then Exit Sub
Range("d10:d12").ClearContents
'if you want to remove the validation entirely change to .clear instead of
..clearcontents
End Sub

--
Don Guillett
SalesAid Software

"max power" wrote in message
...
i need a macro that will make a cell value blank when another cell is
changed.

i cant use a formula because the cell that needs to be blank has data
validation on it and so takes entry from a list

short example of what i want is:

if cell D9 is changed in any way then cells D10,D11 and D12 will be
cleared
( = "")

please help