+ Reply to Thread
Results 1 to 11 of 11

Modifying a SelectionChange event based on Key pressed?

  1. #1
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    349

    Modifying a SelectionChange event based on Key pressed?

    With the help of this forum, in the past I created a Select Case statement based on which key or combination of keys were pressed when a macro is launched. Originally the macro launched from a button.

    I’m trying to modify some SelectionChange code to behave differently depending upon which button was pressed when the selection was changed. Big picture it’s working as intended, passing the variable I need in the ThisWorkbook module to differentiate the code.

    The one problem I haven’t been able to solve is the normal Excel behavior when you hold down Shift/Ctrl/Alt and clicking on a second cell on a worksheet. I really prefer to use the Shift key for my plan, but that highlights a cell range rather than just the cell I click on. I was hoping that Target.Address would give me the cell clicked address, but unfortunately it returns the highlighted range. Either the Alt key or Shift+Ctrl appear to work, but I’d really prefer to just the single Shift key. You can see the (mis)behavior in the attached workbook by clicking on different cells, with different keys depressed.

    Any ideas how to use the Shift key in this context and have it only select the specific cell clicked?

    Thanks for reading and any ideas?
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Modifying a SelectionChange event based on Key pressed?

    Hi aquinxano,

    I think your are looking for something like
    Application.InputBox( , Type:=8)

    I used to think you needed to type stuff in the input box but this type 8 allows you to click on a cell and it remembers the address.

    I don't quite understand your question but I think you want to click on a cell and have it remember the cell address in VBA?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Modifying a SelectionChange event based on Key pressed?

    The active cell is the cell that you clicked on.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    349

    Re: Modifying a SelectionChange event based on Key pressed?

    Hi MarvinP,
    Not sure I see how I can resolve my problem with an inputbox, but that is likely because I didn’t define the question well enough in the first place. Let me try and clarify.

    If you open the attached workbook, Sheet1, cell F11 is selected. If you hold down the Shift key and click in cell G1, the range F1:G11 is highlighted, and the macro runs telling me what, if any key was down (Shift =4). I want to be able to do some stuff on the row of the cell selected (G1), but the Activecell remains F11 and the Target.Address is F1:G11. I cannot figure out how to identify cell G1 was clicked when the Shift key is the one pressed.

    (Compare to holding down the Alt key and clicking G1. G1 is highlighted the macro returns 1 (Alt =1) and the Target.Address is G1. That works the way I want except I would prefer to use the Shift Key rather than the Alt key (more convenient for my keyboard), and I plan to have at least 1, possible two more options, so in the end I could have three single key combinations.

    Hope this better explains my question. Thanks for reading.

  5. #5
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    349

    Re: Modifying a SelectionChange event based on Key pressed?

    mehmetcik - not when you're holding down the Shift key when you click - the activecell remains the first cell of the resulting range selected.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Modifying a SelectionChange event based on Key pressed?

    Hi aquixano

    This Code will find it for you...what do you wish to do with it...

    Please Login or Register  to view this content.
    Last edited by jaslake; 08-19-2017 at 08:01 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Modifying a SelectionChange event based on Key pressed?

    Hi aquixano,

    Instead of using a new .dll, I think you should select a cell or range of cells or Areas of ranges of cells. Then create a macro that handles these three different combinations using
    Selection.Cells.Count
    Selection.Areas.Count

    Then put this handler in a module and create a QAT button to call it, or assign a key combination to call the routing.

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Modifying a SelectionChange event based on Key pressed?

    I see.

    So if you create a variable to store the active cell address when the sheet is activated, defaulting to A1

    If the Selection Changes to A1:C5

    Then the last cell selected is C5. Right?

    S1 in the code below becomes the previous cell, S2 is the last clicked cell

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 08-19-2017 at 08:37 PM.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Modifying a SelectionChange event based on Key pressed?

    @mehmetcik,

    In the OP's attached he imply they want to handle single cells, ranges of cells and multiple ranges of cells (these objects are named AREAS).

    I don't think he will accomplish the task with a keydown and/or keyup through the keyboard buffer.

    I believe he should select the cells and ranges and areas before calling the routine, using our normal way of selecting ranges of cells (holding down Ctrl key and selecting more stuff).

    Then he really wants a sub in the main module that will loop through all possible Areas using Area indexes and then through the ranges in the areas and finally the cells in the ranges. Something like:

    For var =1 to selection.areas.count
    For each rng in areas(var)
    'whatever he wants to do
    next rng
    next var

    To see: select a bunch of different ranges of cells and in the immediate window do:
    ?Selection.Areas.Count
    ?Selection.Areas(1).address

    I think this will get the OP closer to what he wants. I'm still unsure what the goal is, but I do know that selecting ranges of cells (using the standard) method will be better than trying to creating his own way.
    Last edited by MarvinP; 08-19-2017 at 10:52 PM.

  10. #10
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    349

    Re: Modifying a SelectionChange event based on Key pressed?

    MarvinP – Thanks for your feedback – I didn’t realize / understand that I was creating a new .dll. Sorry I didn’t explain my intent adequately. Let me give you a simple example of what I’m trying to accomplish. Using worksheet selectionchange, anytime you click on a single cell in col B, say a msgbox would say “Hello.” But if I hold down the Shift key when clicking on the exact same cell in Col B, the msgbox would say “good bye.” Holding down the Ctrl & Alt keys would do two other different things. I want the selection itself to trigger the macro, not select and then do something additional to launch the macro.

    Mehmetcik – Thank you as well for your input. I was gone a couple days and have been playing with your suggestion for a couple. I’m close, but stuck on one part – here is your code which I have modified. I have also attached the latest file (I’ve commented out all the code for a normal click, and Ctrl+click)

    Please Login or Register  to view this content.
    I’m not sure how the Worksheet_Activate code is supposed to work. Almost all of the time I will already be on the worksheet, so I won’t be activating it. But I’ve commented it out and it still seems to work ok.

    When the Shift key is pressed, a contiguous range defined by the top left cell to the bottom right cell is selected. I can test to see if the activecell.address is equal to t(0), or t(1) using a Select Case statement. In the range N2:O3 on Sheet1, if you select cell N2, hold down the Shift and click on O3, the code returns O3 (the last cell selected – as desired). Similarly, if you select O3, hold Shift and select N2, it returns N2. I cannot figure out how to do it when N3:O2 or O2:N3 is selected. Regardless of how it was selected the Target address is N2:O3, and if the command “range("$N$3:$O$2").copy” is valid despite the non-conventional range definition. Also, since each of the four selection combination recognizes the correct “activecell.address” (the first cell selected), it seems like there should be a way to find the last two combinations – but I’m stuck. If you’re still watching this thread and have any suggestions, I’d appreciate the additional guidance. Thanks.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Modifying a SelectionChange event based on Key pressed?

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to prevent SelectionChange event firing before Change event?
    By franklyn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2012, 05:17 AM
  2. [SOLVED] VBA + SelectionChange event
    By HuskerBronco in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-13-2011, 06:36 PM
  3. Unexpected SelectionChange event code
    By bristly in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-28-2008, 10:33 AM
  4. Worksheet SelectionChange Event
    By mjack003 in forum Excel General
    Replies: 2
    Last Post: 05-08-2006, 03:35 PM
  5. Disable SelectionChange Event
    By BillCPA in forum Excel General
    Replies: 2
    Last Post: 02-17-2006, 02:50 PM
  6. [SOLVED] SelectionChange event
    By Kate in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2005, 02:10 PM
  7. SelectionChange event
    By Hayeso in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2005, 11:05 AM
  8. Worksheet SelectionChange event
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-23-2005, 08:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1