I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!

This is untested:

Private Sub Worksheet_Change()
If Range("C1")= "No Change" Then
Range("A1") = ""
Range("B2") = ""
End If
End Sub

The dots are normally used in conjunction with a "With" statement and to get
a null value in the cell, omit the space between the quote marks. Also the
code will work better in a Worksheet code module as opposed to the
ThisWorkbook module.



"Daviv" wrote:

I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!

JLGWhiz,

I tried what you recommend. It does not worked. Anything additional help
welcome.
--
Thanks!


"JLGWhiz" wrote:

This is untested:

Private Sub Worksheet_Change()
If Range("C1")= "No Change" Then
Range("A1") = ""
Range("B2") = ""
End If
End Sub

The dots are normally used in conjunction with a "With" statement and to get
a null value in the cell, omit the space between the quote marks. Also the
code will work better in a Worksheet code module as opposed to the
ThisWorkbook module.



"Daviv" wrote:

I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!

JLG,

It did not worked. Anymore help welcome.

--
Thanks!


"JLGWhiz" wrote:

This is untested:

Private Sub Worksheet_Change()
If Range("C1")= "No Change" Then
Range("A1") = ""
Range("B2") = ""
End If
End Sub

The dots are normally used in conjunction with a "With" statement and to get
a null value in the cell, omit the space between the quote marks. Also the
code will work better in a Worksheet code module as opposed to the
ThisWorkbook module.



"Daviv" wrote:

I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!

To put under Worksheet Change Event

If target.column=3 and target.row=1 then
if target.value="No Change" then
target.offset(1,-1) = ""
target.offset(,-2)=""
end If
end if

Are you getting an error?

If not, then you probably have events disabled. Put this code in a general
module (insert = Module in the vbe)

Sub turnOnEvents()
application.EnableEvents = True
End sub

Now run it with Tools=Macro=Macros, select TurnOnEvents and click Run.

then right click on the sheet tab, select view code, and put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" then
if Instr(1,application.Trim(Target),"no change", _
vbTextCompare) then
me.Range("A1,B2").Clearcontents
end if
end if
End sub

--
Regards,
Tom Ogilvy

"Daviv" wrote:

JLGWhiz,

I tried what you recommend. It does not worked. Anything additional help
welcome.
--
Thanks!


"JLGWhiz" wrote:

This is untested:

Private Sub Worksheet_Change()
If Range("C1")= "No Change" Then
Range("A1") = ""
Range("B2") = ""
End If
End Sub

The dots are normally used in conjunction with a "With" statement and to get
a null value in the cell, omit the space between the quote marks. Also the
code will work better in a Worksheet code module as opposed to the
ThisWorkbook module.



"Daviv" wrote:

I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!

Kemal, Your codes works Thanks. And Thanks everybody for your input.
--
Thanks!


" wrote:

To put under Worksheet Change Event

If target.column=3 and target.row=1 then
if target.value="No Change" then
target.offset(1,-1) = ""
target.offset(,-2)=""
end If
end if

Daviv, Is the text "No Change" a message in the validation box or is it a
text value in the cell?

"Daviv" wrote:

JLG,

It did not worked. Anymore help welcome.

--
Thanks!


"JLGWhiz" wrote:

This is untested:

Private Sub Worksheet_Change()
If Range("C1")= "No Change" Then
Range("A1") = ""
Range("B2") = ""
End If
End Sub

The dots are normally used in conjunction with a "With" statement and to get
a null value in the cell, omit the space between the quote marks. Also the
code will work better in a Worksheet code module as opposed to the
ThisWorkbook module.



"Daviv" wrote:

I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!

No Change is 1 of the values in the listbox generated from a validation.
--
Thanks!


"JLGWhiz" wrote:

Daviv, Is the text "No Change" a message in the validation box or is it a
text value in the cell?

"Daviv" wrote:

JLG,

It did not worked. Anymore help welcome.

--
Thanks!


"JLGWhiz" wrote:

This is untested:

Private Sub Worksheet_Change()
If Range("C1")= "No Change" Then
Range("A1") = ""
Range("B2") = ""
End If
End Sub

The dots are normally used in conjunction with a "With" statement and to get
a null value in the cell, omit the space between the quote marks. Also the
code will work better in a Worksheet code module as opposed to the
ThisWorkbook module.



"Daviv" wrote:

I have a cell containing a validation listbox. I want to write a code such
that if the user select "No Change", Cell A1 and B2 becomes blank. Below is
my code which is not working:

Private Sub Workbook_Change()
If .Range("C1")= "No Change" Then
.Range("A1") = " "
.Range("B2") = " "
End If
End Sub

Thanks for your help in advance.

--
Thanks!