Trigger "Worksheet_Change" for specific column?

G

Guest

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
 
J

Jim Cone

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


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
 
G

Guest

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
 
J

Jim Cone

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


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
 
G

Guest

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

Florence

Jim Cone said:
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


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 said:
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


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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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


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 said:
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


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
Click to expand...
Click to expand...
 
N

Norman Jones

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

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

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


---
Regards,
Norman



Florence said:
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 said:
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


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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oh sorry!! I got it!!

Thx agani!

Florence

Norman Jones said:
Hi Florence,
Have some problem for the "If Target.Address Like "$A$#" Then" statement:
Click to expand...
You have changed Jim's statement, which was:

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

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


---
Regards,
Norman



Florence said:
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 said:
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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
Hi Florence,
Have some problem for the "If Target.Address Like "$A$#" Then"
Click to expand...
statement:
You have changed Jim's statement, which was:

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

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


---
Regards,
Norman



Florence said:
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 said:
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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
N

Norman Jones

Hi Bob,
Why be clear when you can be obtuse :)?
Click to expand...

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 said:
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 said:
Hi Florence,
Have some problem for the "If Target.Address Like "$A$#" Then"
Click to expand...
statement:
You have changed Jim's statement, which was:
If Target.Address Like "$A$#*" Then
Click to expand...

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

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


---
Regards,
Norman



Florence said:
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

:

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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
Hi Bob,
Why be clear when you can be obtuse :)?
Click to expand...

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 said:
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 said:
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



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

:

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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Hi Norman,

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

Bob


Norman Jones said:
Hi Bob,
Why be clear when you can be obtuse :)?
Click to expand...

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 said:
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 said:
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



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

:

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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

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

Regards

Bob


Florence said:
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 said:
Hi Bob,
Why be clear when you can be obtuse :)?
Click to expand...

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 said:
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)


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



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

:

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


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

:

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


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to run excel vba code when cell value changes from empty to entered value. 1
Worksheet_SelectionChange & Worksheet_Change results in different Targets 2
More then 1 worksheet_change event 8
Combine Two Different Worksheet_Change codes 9
Excel Worksheet_change with 2 functions to insert date and hyperlink window 0
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0
Worksheet_Change help 6
Array coding type mismatch 6

Top