Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trigger code from cell click (2000)

    Hi All,

    How do I trigger code when a user clicks a specific cell?

    (Currently, code runs from button click, but I need to variably place the button (i.e. at the end of a list) and figured it would be ease to trigger event from a "cell click".)

    Thanks in advance,

    Rob.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Trigger code from cell click (2000)

    If the cell to be clicked changes as a result of row or column inserts, range name it and use the Worksheet event something like this:

    Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("RangeNamed Cell")) Then
    Call MySub ' or insert code here
    End If
    End Sub

    Search the Excel Forum for Legare's excellent and more complete code examples using the Worksheet_Change() event.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    One possible problem with this approach. The code will also execute if the cell is entered by tabbing into it, not just when clicked. If that is a problem, the only other possible other solution is to use the Worksheet_BeforeDoubleClick event, but then it would require double clicking in the cell to trigger the code. I don't know of a way to trigger the code exclusively on a single click.
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    Thank You John and Legare,

    I think I can utilize the "BeforeDoubleClick" event to trigger the event. I have also read Legare's posts re: this.

    Can either of you explain how this event evaluates with a specific cell, or condition within the cell.

    For instance, assume a list, 8 columns wide and of varring lengths. There is a note indicating 'End of List' in column A, 2 lines after the end of the list. When a user double clicks in this cell (Col. A, 2 rows past list end, with text 'End of List') I would like to trigger the event.

    Thanks,

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Trigger code from cell click (2000)

    Robert, the doubleclick event is triggered by the user double-clicking on the cell; the programming of recognition of the event is simple. To start, right click the worksheet tab of the sheet in question, click View Code, in the code window (right side), click the object drop-down at the top left, and select Worksheet, then on the right side procedure drop-down, select BeforeDoubleClick. The event is created for you, but it contains no code, which is the fun part.

    The question for you is how you set up and retain that cell to be doubleclicked. Does it exist when the user opens the Workbook, can the cell be overwritten, how does it maintain it's position as the last used cell in Column A?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Trigger code from cell click (2000)

    Good point, and the code would also trigger if a user merely cursored into the cell. Double-Click is the way to go.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    The event is triggered for a double click anywhere on the worksheet, and the parameter "Target" passed to the event routine will be the cell that was closest to the double click. Your code has to determine if it was on a cell that you are interested in. In the code below I first Set the object variable oCell equal to the cell that is two below that last cell in column A that contains something. Then I use the Intersect function in an if statement to see if that was the cell double clicked on.

    <pre>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim oCell As Range
    Cancel = True
    With ActiveSheet
    Set oCell = .Range("A1").Offset(.Range("A1").Offset(.UsedRange .Row + _
    .UsedRange.Rows.Count, 0).End(xlUp).Row + 1, 0)
    If Intersect(Target, oCell) Is Nothing Then Exit Sub
    'Your code goes here.
    End With
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    John, can you tell me why the following code crashes with a 'type mismatch' error wherever I click EXCEPT when the target IS inside indexblock (a named range on the sheet) AND the cell is empty - when that's the case it displays the msgbox saying "It's not in it", which should display when it's NOT within the named range. The other msgbox never displays at all. I'm completely confused and I can't see what's wrong!

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Intersect(Target, Range("indexblock")) Then
    MsgBox "It's in it"
    Else
    MsgBox "It's not in it"
    End If

    End If
    End Sub

    I tried putting Range() around 'Target' but that just crashes on the 'if intersect ...' line with the error "Method 'Range' of object '_Worksheet' failed" ... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Many thanks!
    Beryl M


  9. #9
    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: Trigger code from cell click (2000)

    Your code has an extra END IF
    It also does does not compare the "intersect range" to anything

    How about this?

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("indexblock")) Is Nothing Then
    MsgBox "It's in it"
    Else
    MsgBox "It's not in it"
    End If
    End Sub</pre>


    Steve

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    Sorry about the extra END IF, that was a leftover from removing the code that actually does something before posting!

    It was the "Is Nothing" I needed - for some reason the sample code JohnBF posted above doesn't have that in it so I didn't realise it needed it!

    Many thanks
    Beryl M


  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    This <img src=/w3timages/censored.gif alt=censored border=0> code is driving me mad! Having got the rest working properly, I just added an error trap, and for some reason the sub is running straight through the error trap at the end of the sub every time, whether there's an error or not! What have I done now?!!! (I haven't removed the content this time so I there shouldn't be any more red herrings)!

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim newsheet As String

    On Error GoTo wrongName
    If Not Intersect(Target, Range("indexblock")) Is Nothing Then
    If ActiveCell <> "" Then
    newsheet = Mid(ActiveCell, Len(ActiveCell) - 8, 8)
    Worksheets(newsheet).Select
    End If
    End If

    wrongName:
    MsgBox "Are you sure this name complies with the notes below? Please check and try again.", vbInformation
    Exit Sub

    End Sub
    Beryl M


  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trigger code from cell click (2000)

    Beryl,
    Try:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim newsheet As String

    On Error GoTo wrongName
    If Not Intersect(Target, Range("indexblock")) Is Nothing Then
    If ActiveCell <> "" Then
    newsheet = Mid(ActiveCell, Len(ActiveCell) - 8, 8)
    Worksheets(newsheet).Select
    End If
    End If
    Exit Sub
    wrongName:
    MsgBox "Are you sure this name complies with the notes below? Please check and try again.", vbInformation


    End Sub

    Also, as your sub appears to be retrieving the last 8 characters, you could just use:
    Right$(ActiveCell.Value, 8)
    instead of the Mid statement.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Trigger code from cell click (2000)

    Beryl, <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> the code I posted is poor for the very reason you found. The "If Not Intersect(target, range) Is Nothing" is the way to go because handles the situation where there is no intersection without erroring out.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Trigger code from cell click (2000)

    --------------------------
    Also, as your sub appears to be retrieving the last 8 characters, you could just use:
    Right$(ActiveCell.Value, 8)
    instead of the Mid statement.
    --------------------------

    Umm, it does look like that, doesn't it? But in fact it isn't, and it gave me yet another headache before I realised, too. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Put numbers in instead and you'll see what I mean: eg string length = 20 characters. 20 - 8 = 12. Then tell it to use the 8-character string starting at character 12, and you get a string using characters 12-19, not characters 13-20!

    Needless to say, I wanted the last 8 characters *apart from* the final one on a variable length string!
    Beryl M


  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trigger code from cell click (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/doh.gif border=0 alt=doh width=15 height=15>I really am having one of those days! Sorry!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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