Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Alternative to Worksheet_SelectionChange (VBA/Excel/2000)

    I am calling a macro by selecting Cell E17 with the following code. I am concerned that the user will inadvertently select Cell E17 with a Tab or Enter key. I would like to require a Click on Cell E17, but have not been able to develop or locate an appropriate piece of code.
    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If ActiveCell.Address = "$E$17" Then
    Call Populate_Review_Report.CaptureFromWordDoc
    End If
    Application.EnableEvents = True
    End Sub
    </pre>


    Any guidance will be appreciated.
    Regards
    Don

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

    Re: Alternative to Worksheet_SelectionChange (VBA/Excel/2000)

    As far as Excel is concerned, it doesn't matter how the user selects a cell, with the mouse or using the keyboard. I don't think you can distinguish between them.

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

    Re: Alternative to Worksheet_SelectionChange (VBA/Excel/2000)

    You could us a double click on cell E17 with code like this:

    <code>
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("E17")) Is Nothing Then
    Application.EnableEvents = False
    Call Populate_Review_Report.CaptureFromWordDoc
    Application.EnableEvents = True
    End If
    End Sub
    </code>
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Alternative to Worksheet_SelectionChange (VBA/Excel/2000)

    Thank you Hans, I had come to the same conclusion. But Legare has provided us with a cunning approach.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Alternative to Worksheet_SelectionChange (VBA/Excel/2000)

    Thank you Legare. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    Regards
    Don

Posting Permissions

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