Results 1 to 7 of 7
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    If function or Change Event? (2002)

    I am trying to work out the following
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If function or Change Event? (2002)

    Hi Jim,

    I think you need the Worksheet_Change event for this:

    - Activate the Visual Basic Editor (Alt+F11)
    - Activate the Project Explorer (Ctrl+R)
    - If necessary, expand your workbook until you see the worksheet in the tree view.
    - Double click the worksheet.
    - Type or copy the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D20")) Is Nothing Then
    If LCase(Range("D20")) = "yes" Then
    Range("E20") = "Yes"
    End If
    End If
    End Sub

    Comparisons in VBA are case-sensitive by default, so the code tests on LCase(Range("D20")) to allow "yes" and "YES" and "Yes".

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If function or Change Event? (2002)

    You might use a deliberate circular reference.
    Go to tools, options, calculation and check the iteration box.

    Change the formula to (assumed it is in cell A!):

    =If(D20="Yes","Yes",A1)

    One culprit though: it only works if iteration is set. Since this option is a systemwide setting, you need to explicitly set it in the workbook_Open event in the thisworkbook module (and restore the previous setting in the Before_Close routine). Otherwise the user might be presented with the circ error message.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If function or Change Event? (2002)

    Thanks Hans. Your code does the job well and I learned a new function, Intersect. I took your code and expanded it to a range of cells (D2029).

    I used a variable declared as object. Object can be any object so I believe it is late binding. There is no type Cell. Is there a way to change the variable to early binding? Not that this example needs it but just to learn more.

    Secondly, my code used For Each
    You know it's time to diet when you push away from the table and the table moves.

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If function or Change Event? (2002)

    Hi Jan,

    Thanks for the help. I tried out your code also and I did not need to change any options. I stored the formula in E20 but used E40 instead of A1. E40 is where I store the value "No".

    Both suggestions work. Thank you!
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If function or Change Event? (2002)

    You are correct that there is no Cell object; use the Range object instead. You can use

    Dim objCell As Range

    With this solution, I don't think there is an alternative to looping through the cells, but if there is one, I'm sure one of the other Loungers will point it out.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If function or Change Event? (2002)

    I just want to "back up" Hans that there is no better way to loop thru all the cells in the range. The only improvement could be if the range could contain something other than text. In that case you could make the "loop range" smaller with the specialcells method.

    Other approaches would be to use the FIND method, but with this small of range I would think the extra coding might not be worth it and this looping (at least to me) seems more intuitive.

    Steve

Posting Permissions

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