+ Reply to Thread
Results 1 to 7 of 7

[SOLVED] Switch cells on input

  1. #1
    Dan
    Guest

    [SOLVED] Switch cells on input

    I'm using Worksheet_Change for a range d6:l17 . What I am trying to
    accomplish is if 1 is entered in F and a 1 is entered in H both contence is
    cleared and a 1 is placed on d.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Target
    If Range(Cells(.Row, 6)) And Range(Cells(.Row, 8)).Value = 1 Then
    Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
    Range(Cells(.Row, 4)).Value = 1
    End If
    End With
    CleanUp:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    ??? Thanks for the help




  2. #2
    Registered User
    Join Date
    07-03-2004
    Posts
    49

    Re :Switch cells on input

    Dan

    I don't think you can get what you want to do by doing what you are doing?

    A couple of reasons :-

    1. When you are entering some data in a cell, your selection or target range will always be 1 cell. So you event procedure will never go beyond line 1 since you cell count will never be greater than 1.

    2. You can never enter any data in more than 1 cell at any one time from the front end.

    What you can do is write a Worksheet_Change event procedure and check for the contents of cells in columns 'F' and 'H' for the same row say contents of cells(5,6) and cells(5,8) and if both these cells contain a value of 1, then clear both these cells and insert a 1 in cell(5,4).

    However, you will need to disable your event procedures before you make any changes to any cell to avoid the cascading effect. I take it that you are aware of the two ways of doing so viz Disable applicationEvents or create a static variable and insert the corresponding code as the first line of your event procedure.


    Best regards


    Deepak Agarwal

  3. #3
    chijanzen
    Guest

    RE: Switch cells on input

    Dan:

    try

    With Target
    If Cells(.Row, 6) = 1 And Cells(.Row, 8).Value = 1 Then
    Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
    Cells(.Row, 4).Value = 1
    End If
    End With

    --
    天行健,君*以自強不息
    地勢坤,君*以厚德載物

    http://www.vba.com.tw/plog/


    "Dan" wrote:

    > I'm using Worksheet_Change for a range d6:l17 . What I am trying to
    > accomplish is if 1 is entered in F and a 1 is entered in H both contence is
    > cleared and a 1 is placed on d.
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > If Target.Cells.Count > 1 Then Exit Sub
    > If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
    > On Error GoTo CleanUp
    > Application.EnableEvents = False
    > Application.ScreenUpdating = False
    > With Target
    > If Range(Cells(.Row, 6)) And Range(Cells(.Row, 8)).Value = 1 Then
    > Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
    > Range(Cells(.Row, 4)).Value = 1
    > End If
    > End With
    > CleanUp:
    > Application.EnableEvents = True
    > Application.ScreenUpdating = True
    > End Sub
    >
    > ??? Thanks for the help
    >
    >
    >


  4. #4
    Rowan Drummond
    Guest

    Re: Switch cells on input

    And if you don't want to also clear the contents of the cell in column G
    then try:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Target
    If Cells(.Row, 6).Value = 1 _
    And Cells(.Row, 8).Value = 1 Then
    Cells(.Row, 6).ClearContents
    Cells(.Row, 8).ClearContents
    Cells(.Row, 4).Value = 1
    End If
    End With
    CleanUp:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    Hope this helps
    Rowan

    Dan wrote:
    > I'm using Worksheet_Change for a range d6:l17 . What I am trying to
    > accomplish is if 1 is entered in F and a 1 is entered in H both contence is
    > cleared and a 1 is placed on d.
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    >
    > If Target.Cells.Count > 1 Then Exit Sub
    > If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
    > On Error GoTo CleanUp
    > Application.EnableEvents = False
    > Application.ScreenUpdating = False
    > With Target
    > If Range(Cells(.Row, 6)) And Range(Cells(.Row, 8)).Value = 1 Then
    > Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
    > Range(Cells(.Row, 4)).Value = 1
    > End If
    > End With
    > CleanUp:
    > Application.EnableEvents = True
    > Application.ScreenUpdating = True
    > End Sub
    >
    > ??? Thanks for the help
    >
    >
    >


  5. #5
    Rowan Drummond
    Guest

    Re: Switch cells on input

    Hi Deepak

    > 1. When you are entering some data in a cell, your selection or target
    > range will always be 1 cell. So you event procedure will never go
    > beyond line 1 since you cell count will never be greater than 1.


    The line of code is "If Target.Cells.Count > 1 Then Exit Sub" so it will
    only exit if more than one cell is changed at a time. Usually used to
    trap users pasting a range, deleting a row etc.

    > 2. You can never enter any data in more than 1 cell at any one time
    > from the front end.


    Select a range of cells on a sheet. Type some data and hit Ctrl+Enter.
    You've just entered the same data in the entire range all at the same time.

    > However, you will need to disable your event procedures before you make
    > any changes to any cell to avoid the cascading effect.


    The Application.EnableEvents commands are included in the original event.

    Kind Regards
    Rowan

    agarwaldvk wrote:
    > Dan
    >
    > I don't think you can get what you want to do by doing what you are
    > doing?
    >
    > A couple of reasons :-
    >
    > 1. When you are entering some data in a cell, your selection or target
    > range will always be 1 cell. So you event procedure will never go
    > beyond line 1 since you cell count will never be greater than 1.
    >
    > 2. You can never enter any data in more than 1 cell at any one time
    > from the front end.
    >
    > What you can do is write a Worksheet_Change event procedure and check
    > for the contents of cells in columns 'F' and 'H' for the same row say
    > contents of cells(5,6) and cells(5,8) and if both these cells contain a
    > value of 1, then clear both these cells and insert a 1 in cell(5,4).
    >
    > However, you will need to disable your event procedures before you make
    > any changes to any cell to avoid the cascading effect. I take it that
    > you are aware of the two ways of doing so viz Disable applicationEvents
    > or create a static variable and insert the corresponding code as the
    > first line of your event procedure.
    >
    >
    > Best regards
    >
    >
    > Deepak Agarwal
    >
    >


  6. #6
    Greg Wilson
    Guest

    RE: Switch cells on input

    Try:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim x As Integer

    If Target.Cells.Count > 1 Then Exit Sub
    Set rng = Union(Range("F6:F17"), Range("H6:H17"))
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    x = IIf(Target.Column = 6, 3, -1)
    If Target.Value = 1 And Target(1, x) = 1 Then
    Union(Target, Target(1, x)).ClearContents
    Cells(Target.Row, 4) = 1
    End If
    CleanUp:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    Regards,
    Greg

  7. #7
    Registered User
    Join Date
    07-03-2004
    Posts
    49

    RE: Switch cells on input

    Hi Guys

    Apologies here - forgot about the Ctrl+Shift+Enter

    I was thinking about selecting a cell to enter values.


    Best regards


    Deepak Agarwal

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1