Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Detecting cells (2003)

    Is there any way I can detect that a cell is clicked - so that when a user clicks in a cell it runs a macro, based on the contents of the clicked cell?
    Thanks

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

    Re: Detecting cells (2003)

    Right-click the sheet tab and select View Code from the popup menu.
    There's a dropdown list above the blinking insertion point that contains (General).
    Select Worksheet from this dropdown list.
    This will create the following code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub

    This code will run each time the user selects a cell or range of cells. Target represents the cell(s).
    You can write code that uses Target. Example:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then
    MsgBox "You selected cell " & Target.Address & " with value " & Target.Value
    Else
    MsgBox "You selected more than one cell"
    End If
    End Sub

    If you want to react to selecting a specific cell (or range), you can do it like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("A3"), Target) Is Nothing Then
    MsgBox "Cell A3 has value " & Range("A3")
    End If
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting cells (2003)

    Thanks
    Allison

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

    Re: Detecting cells (2003)

    <!profile=LegareColeman>LegareColeman<!/profile> pointed out to me (thanks, Legare!) that I forgot to mention an important point: the Worksheet_SelectionChange event procedure will be executed whenever the user moves to another cell, whether by clicking on a cell or by using the keyboard (for example pressing an arrow key, or tab, etc.)
    You might use the Worksheet_BeforeDoubleClick event instead; this occurs when the user double clicks a cell.

Posting Permissions

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