Dear all,

I have the following script that change the value for the cell B1 according
to A1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If

How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)

Thanks in advance.

Florence

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1 according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)
Thanks in advance.
Florence

Thanks Jim,

Have tested your codes and it works on a blank sheet.

However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?

*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1 according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)
Thanks in advance.
Florence

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1 according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)
Thanks in advance.
Florence

Thanks so much Jim!! It works now!!!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1 according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)
Thanks in advance.
Florence

Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then" statement:

If I set "$A$#", this statement will return False after the 10th row. i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1 according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the whole
column B according to the whole column A" (i.e. A2 controls B2, A3 controls
B3, and so on.)
Thanks in advance.
Florence

Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then" statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then" statement:

If I set "$A$#", this statement will return False after the 10th row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the
whole
column B according to the whole column A" (i.e. A2 controls B2, A3
controls
B3, and so on.)
Thanks in advance.
Florence

Oh sorry!! I got it!!

Thx agani!

Florence

"Norman Jones" wrote:

Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then" statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then" statement:

If I set "$A$#", this statement will return False after the 10th row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the
whole
column B according to the whole column A" (i.e. A2 controls B2, A3
controls
B3, and so on.)
Thanks in advance.
Florence

Why be clear when you can be obtuse :-)?

What is wrong with

If Target.Column = 1 Then

no chance then of forgetting the syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" wrote in message
...
Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this
case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:

If I set "$A$#", this statement will return False after the 10th row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End With]
structure as I have another [ With .Validation ... End With ] inside
the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the
whole
column B according to the whole column A" (i.e. A2 controls B2, A3
controls
B3, and so on.)
Thanks in advance.
Florence

Hi Bob,

Why be clear when you can be obtuse :-)?

I accept no plaudits for clarity, and no accusation of obtuseness - the
code is not mine.

There are enough examples of my obtuseness for it to be unnecessary to
ascribe to me the imagined failings of others. <VBG

I merely drew the OP's attention to the fact that the author's code worked
in its unabridged form.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Why be clear when you can be obtuse :-)?

What is wrong with

If Target.Column = 1 Then

no chance then of forgetting the syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" wrote in message
...
Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this
case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:

If I set "$A$#", this statement will return False after the 10th row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End
With]
structure as I have another [ With .Validation ... End With ] inside
the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the
whole
column B according to the whole column A" (i.e. A2 controls B2, A3
controls
B3, and so on.)
Thanks in advance.
Florence

Hello Norman, Bob and Jim,

I do appreciate for the sharing from ALL OF YOU. As a VBA beginner, I
enjoy all of your suggestions so that I get better understanding for various
methods.

Thank you for your contribution.

Best wishes,

Florence

"Norman Jones" wrote:

Hi Bob,

Why be clear when you can be obtuse :-)?

I accept no plaudits for clarity, and no accusation of obtuseness - the
code is not mine.

There are enough examples of my obtuseness for it to be unnecessary to
ascribe to me the imagined failings of others. <VBG

I merely drew the OP's attention to the fact that the author's code worked
in its unabridged form.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Why be clear when you can be obtuse :-)?

What is wrong with

If Target.Column = 1 Then

no chance then of forgetting the syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" wrote in message
...
Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this
case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:

If I set "$A$#", this statement will return False after the 10th row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End
With]
structure as I have another [ With .Validation ... End With ] inside
the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the
whole
column B according to the whole column A" (i.e. A2 controls B2, A3
controls
B3, and so on.)
Thanks in advance.
Florence

Hi Norman,

I know :-). Yours was just the last post of any relevance (there you are,
finish on a positive :-))

Bob


"Norman Jones" wrote in message
...
Hi Bob,

Why be clear when you can be obtuse :-)?

I accept no plaudits for clarity, and no accusation of obtuseness - the
code is not mine.

There are enough examples of my obtuseness for it to be unnecessary to
ascribe to me the imagined failings of others. <VBG

I merely drew the OP's attention to the fact that the author's code worked
in its unabridged form.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Why be clear when you can be obtuse :-)?

What is wrong with

If Target.Column = 1 Then

no chance then of forgetting the syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" wrote in message
...
Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in this
case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:

If I set "$A$#", this statement will return False after the 10th row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th
and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End
With]
structure as I have another [ With .Validation ... End With ] inside
the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for the
whole
column B according to the whole column A" (i.e. A2 controls B2,
A3
controls
B3, and so on.)
Thanks in advance.
Florence

Exactly Florence, that is why we chip in with these alternatives.

Regards

Bob


"Florence" wrote in message
...
Hello Norman, Bob and Jim,

I do appreciate for the sharing from ALL OF YOU. As a VBA beginner, I
enjoy all of your suggestions so that I get better understanding for
various
methods.

Thank you for your contribution.

Best wishes,

Florence

"Norman Jones" wrote:

Hi Bob,

Why be clear when you can be obtuse :-)?

I accept no plaudits for clarity, and no accusation of obtuseness - the
code is not mine.

There are enough examples of my obtuseness for it to be unnecessary to
ascribe to me the imagined failings of others. <VBG

I merely drew the OP's attention to the fact that the author's code
worked
in its unabridged form.


---
Regards,
Norman



"Bob Phillips" wrote in message
...
Why be clear when you can be obtuse :-)?

What is wrong with

If Target.Column = 1 Then

no chance then of forgetting the syntax

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" wrote in message
...
Hi Florence,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:
You have changed Jim's statement, which was:

If Target.Address Like "$A$#*" Then

Note the asterisk (*) after the hash (#).

The asterisk wildcard acts as a placeholder for any number of (in
this
case)
digits.


---
Regards,
Norman



"Florence" wrote in message
...
Dear Jim,

Have some problem for the "If Target.Address Like "$A$#" Then"
statement:

If I set "$A$#", this statement will return False after the 10th
row.
i.e.
Only workable between A1 .. A9.

If I set "$A$##" , the statement seems return False before the 9th
and
after
the 100 row, and so on. i.e. Only workable between A10 .. A99.

Is there any better method for such If statement?

Thx again!

Florence

"Jim Cone" wrote:

Florence,
Of course it worked <g
'--------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
With Target(1, 2)
.Value = 1
' do other stuff with with
End With
Else
With Target(1, 2)
.Value = 0
' do other stuff with with
End With
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'---------------------------
Jim Cone


"Florence" wrote in message
...
Thanks Jim,
Have tested your codes and it works on a blank sheet.
However, is it possible to reserve the [ With Range("B1") ... End
With]
structure as I have another [ With .Validation ... End With ]
inside
the
Range loop?
*----------------------------------------------------*
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
With .Validation
.Delete
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="99999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be greater than 0!"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("B1")
.Value = 0
With .Validation
.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Must be 0."
.ShowInput = False
.ShowError = True
End With
End With
End If
End If

*----------------------------------------------------*

Much thanks for your help!!

Florence

"Jim Cone" wrote:

Florence,

Something like this ...
'----------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub
'-------------------------------------

Jim Cone
San Francisco, USA


"Florence" wrote in message
...
Dear all,
I have the following script that change the value for the cell
B1
according
to A1:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If IsEmpty(Target.Value) = True Then
With Range("B1")
.Value = 1
Else
With Range("B1")
.Value = 0
End If
End If
How can I modify the script so that I can assign the value for
the
whole
column B according to the whole column A" (i.e. A2 controls B2,
A3
controls
B3, and so on.)
Thanks in advance.
Florence

Hi,

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub

This is the code that has been submitted by Jim Cone. Can somebod
please help me to do the same stuff by using Worksheet_Calculat
instead of Worksheet_Change?

Thanks & regards,
Georg

--
ena_georg
-----------------------------------------------------------------------
ena_george's Profile: http://www.excelforum.com/member.php...fo&userid=2667
View this thread: http://www.excelforum.com/showthread.php?threadid=39572

Worksheet_Calculate doesn't include a Target argument. So, it is not
possible to know what cells have changed because of the recalculation
-- well, at least not without a lot of work.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hi,

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadChange
Application.EnableEvents = False
If Target.Address Like "$A$#*" Then
If Len(Target.Value) = 0 Then
Target(1, 2).Value = 1
Else
Target(1, 2).Value = 0
End If
End If
BadChange:
Application.EnableEvents = True
End Sub

This is the code that has been submitted by Jim Cone. Can somebody
please help me to do the same stuff by using Worksheet_Calculate
instead of Worksheet_Change?

Thanks & regards,
George


--
ena_george
------------------------------------------------------------------------
ena_george's Profile: http://www.excelforum.com/member.php...o&userid=26678
View this thread: http://www.excelforum.com/showthread...hreadid=395721

Hi Tushar,

So actually I will not be able to find a script that will change the
value for the cell B1 according to A1 which is changed following a
calculation, because using Worksheet_Calculate it is not possible to
know what cells have changed?
There is maybe some other possibility to do that?

Thanks & regards,
George


--
ena_george
------------------------------------------------------------------------
ena_george's Profile: http://www.excelforum.com/member.php...o&userid=26678
View this thread: http://www.excelforum.com/showthread...hreadid=395721