Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet_SelectionChange (excel 97)

    (Edited by HansV - inserted <!t>[tab]<!/t> tags to preserve indentation - see <!help=19>Help 19<!/help>)

    When a change is made anywhere in a range ("d3:u31") on the current worksheet
    I want to change the value of cell A1 from "static" to "changed"
    I want to use the A1 value in a macro later on
    After the first change I don't need to know there are more changes, hence the first if statement below.

    This is what I'm trying at the moment. But with no success.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Range("A1") = "static" Then
    If Intersect(Target, ActiveSheet.Range("d3:u31")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Range("A1") = "change"
    Application.EnableEvents = True
    End If
    End If
    End Sub

    Regards

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

    Re: Worksheet_SelectionChange (excel 97)

    1. Use the Worksheet_Change event instead of the Worksheet_SelectionChange event.
    2. Insert Not between If and Intersect.
    3. You don't need Activesheet in this code.

    Ad 1: Worksheet_SelectionChange occurs, as the name implies, when the selection changes, i.e. the user selects cells. Worksheet_Change occurs when the value of cells changes.
    Ad 2: In the code as posted, you test that the Target range has nothing in common with D3:U31, just the opposite of what you want.
    Ad 3: Since the code is in the worksheet module, you don't need to refer to the sheet.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet_SelectionChange (excel 97)

    Hans,

    I only discovered worksheet_change and worksheet_selection recently when searching the lounge for something that would register a change.
    I tried looking elsewhere on the net to understand it, but as always the best advice always comes from the people on the lounge.
    Thank you for explaining it in such detail. I couldn't work out what intersect meant, now it's much clearer.

    Regards

Posting Permissions

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