+ Reply to Thread
Results 1 to 16 of 16

[SOLVED] Trigger "Worksheet_Change" for specific column?

  1. 08-15-2005, 12:05 AM #1
    Florence
    Guest

    [SOLVED] Trigger "Worksheet_Change" for specific column?

    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
    Reply With QuoteRegister To Reply
  2. 08-15-2005, 01:05 AM #2
    Jim Cone
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    news:[email protected]...
    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
    Reply With QuoteRegister To Reply
  3. 08-15-2005, 01:05 AM #3
    Florence
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    Reply With QuoteRegister To Reply
  4. 08-15-2005, 01:05 AM #4
    Jim Cone
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    news:[email protected]...
    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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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
    >
    Reply With QuoteRegister To Reply
  5. 08-15-2005, 03:05 AM #5
    Florence
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > 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
    > >
    >
    Reply With QuoteRegister To Reply
  6. 08-15-2005, 04:05 AM #6
    Florence
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > 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
    > >
    >
    Reply With QuoteRegister To Reply
  7. 08-15-2005, 04:05 AM #7
    Norman Jones
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> 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" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> > 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
    >> >
    >>


    Reply With QuoteRegister To Reply
  8. 08-15-2005, 05:05 AM #8
    Florence
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> 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" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> > 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
    > >> >
    > >>
    >
    >
    >
    Reply With QuoteRegister To Reply
  9. 08-15-2005, 05:05 AM #9
    Bob Phillips
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> 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" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> > 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
    > >> >
    > >>
    >
    >


    Reply With QuoteRegister To Reply
  10. 08-15-2005, 05:05 AM #10
    Norman Jones
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 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" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> 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" <[email protected]> wrote in message
    >> >> > news:[email protected]...
    >> >> > 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
    >> >> >
    >> >>
    >>
    >>
    >
    >


    Reply With QuoteRegister To Reply
  11. 08-15-2005, 06:05 AM #11
    Florence
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > 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" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> 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" <[email protected]> wrote in message
    > >> >> > news:[email protected]...
    > >> >> > 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
    > >> >> >
    > >> >>
    > >>
    > >>
    > >
    > >
    >
    >
    >
    Reply With QuoteRegister To Reply
  12. 08-15-2005, 07:05 AM #12
    Bob Phillips
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    Hi Norman,

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

    Bob


    "Norman Jones" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > 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" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> 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" <[email protected]> wrote in message
    > >> >> > news:[email protected]...
    > >> >> > 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
    > >> >> >
    > >> >>
    > >>
    > >>
    > >
    > >
    >
    >


    Reply With QuoteRegister To Reply
  13. 08-15-2005, 07:05 AM #13
    Bob Phillips
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

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

    Regards

    Bob


    "Florence" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > >> 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" <[email protected]> wrote in message
    > > >> news:[email protected]...
    > > >> > 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" <[email protected]> wrote in message
    > > >> >> news:[email protected]...
    > > >> >> 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" <[email protected]> wrote in message
    > > >> >> > news:[email protected]...
    > > >> >> > 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
    > > >> >> >
    > > >> >>
    > > >>
    > > >>
    > > >
    > > >
    > >
    > >
    > >


    Reply With QuoteRegister To Reply
  14. 08-26-2005, 11:25 AM #14
    ena_george
    ena_george is offline
    Registered User
    Join Date
    08-26-2005
    Posts
    4
    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
    Reply With QuoteRegister To Reply
  15. 08-26-2005, 02:05 PM #15
    Tushar Mehta
    Guest

    Re: Trigger "Worksheet_Change" for specific column?

    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 <[email protected]>,
    [email protected] 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
    >
    >
    Reply With QuoteRegister To Reply
  16. 08-26-2005, 03:52 PM #16
    ena_george
    ena_george is offline
    Registered User
    Join Date
    08-26-2005
    Posts
    4
    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1