Toggling cell entries...ONE LAST QUESTION...
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Toggling cell entries...ONE LAST QUESTION...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Yesterday, Tom Morales and Mudface were kind enough (and smart enough) to help me develop a toggling set of three cells, where entry into one cell automatically blanked the other two. Here's the code...

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents
    If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents
    If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents
    End Sub

    HERE'S MY PROBLEM...

    The above works perfectly. I now have two other cells that I would like to have do the same thing, just with eachother...(not with the three above.) I tried this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents
    If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents
    If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents
    If Target.Address = "$AD$63" Then [AD64].ClearContents
    If Target.Address = "$AD$64" Then [AD63].ClearContents
    End Sub

    Basically I just used the same code for cells AD63 & AD64. The problem lies in that when I try to enter data into either of these cells, it immediately disappears, with both cells remaining empty.

    Any ideas on this?

    Thanks.

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try this one.


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$AD$133" Then [AD134,AD135].ClearContents
    If Target.Address = "$AD$134" Then [AD133,AD135].ClearContents
    If Target.Address = "$AD$135" Then [AD133,AD134].ClearContents
    If Target.Address = "$AD$63" Then [AD64,AD64].ClearContents
    If Target.Address = "$AD$64" Then [AD63,AD63].ClearContents

    End Sub


    suat

  3. #3
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Because if you use [AD64].ClearContents then Change event runs again and this time the target address is just AD64 which force [AD63].ClearContents. But if you use [AD64,AD64].ClearContents then target address would be AD64,AD64 which isnot equal to AD64. Just a little cheat.

    suat

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey, smozgur...

    You're brilliant!

    Thanks mucho.

    bobmc.

  5. #5
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    799
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



  6. #6
    Board Regular
    Join Date
    Jun 2004
    Posts
    242
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    This would be great for my needs
    Is there a set up that would allow only 2 cells of a 3 cell range to contain values.

    ie in a range a1,b1,c1 value is entered in a1 then in b1 and then in c1, when the 3rd value is entered the value in the first cell is cleared out, and so on.

    Hope this makes sense

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com