Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA function (XL2000)

    How can I run VBA code saved on a workbook when one of the cells in the worksheet receives the focus?.

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

    Re: VBA function (XL2000)

    Check out the Workheet_SelectionChange event in the sheet module. The Target argument contains the (new) selection as a range.

  3. #3
    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: VBA function (XL2000)

    This code in a worksheet object will pop a message up whenver cell A1 is selected. Range the reference as appropriate and call a routine if desired instead of popping a messagebox

    Steve
    <pre>Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim rCell As Range
    Set rCell = Range("a1")

    If Not Intersect(Target, rCell) Is Nothing Then
    MsgBox (rCell.Address & " has focus")
    End If
    End Sub</pre>


  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA function (XL2000)

    Thank you Gentlemen for the promptest reply.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Detroit, Michigan, USA
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA function (XL2000)

    On the same note...how can I copy the code avaiable to other cells...that is the exmaples above return the funtion result to the cell...but I'd like to have a formula on the cell so I can copy it over....Any ideas...??
    Thanks agains in advance...

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

    Re: VBA function (XL2000)

    The code provided by Steve is very different from a formula in a cell. This is a worksheet-wide event handler; the SelectionChange event occurswhenever the user changes the selection anywhere in the worksheet, whether by using the arrow keys, keyboard shortcuts such as Ctrl+End or the mouse. You don't need to copy this to cells or something like that.

    Steve's example examines whether cell A1 is part of the selection, but the event handler occurs for all cells in the worksheet. If you could explain in some detail what you would like to do, we may be able to help.

Posting Permissions

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