OnClick in Excel VBA

Question

Is there a way to catch a click on a cell in VBA with Excel  I am not referring to the Worksheet SelectionChange event  as that will not trigger multiple times if the cell is clicked multiple times  BeforeDoubleClick does not solve my problem either  as I do not want to require the user to double click that frequently   My current solution does work with the SelectionChange event  but it appears to require the use of global variables and other suboptimal coding practices  It also seems prone to error

User · Accepted Answer

Clearly  there is no perfect answer  However  if you want to allow the user to    select certain cells   allow them to change those cells  and trap each click even repeated clicks on the same cell    then the easiest way seems to be to move the focus off the selected cell  so that clicking it will trigger a Select event   One option is to move the focus as I suggested above  but this prevents cell editing  Another option is to extend the selection by one cell  left right up down  because this permits editing of the original cell  but will trigger a Select event if that cell is clicked again on its own    If you only wanted to trap selection of a single column of cells  you could insert a hidden column to the right  extend the selection to include the hidden cell to the right when the user clicked and this gives you an editable cell which can be trapped every time it is clicked  The code is as follows   Private Sub Worksheet SelectionChange ByVal Target As Range     prevent Select event triggering again when we extend the selection below   Application EnableEvents   False   Target Resize 1  2  Select   Application EnableEvents   True End Sub

User · Answer

This has worked for me       Private Sub Worksheet Change ByVal Target As Range       If Mid Target Address  3  1        And Mid Target Address  2  1   lt   E  Then          The logic in the if condition will filter for a specific cell or block of cells        Application ScreenUpdating   False         MsgBox  You just changed    amp  Target Address          all conditions are true      DO THE FUNCTION NEEDED         Application ScreenUpdating   True     End If       if clicked cell is not in the range then do nothing  if condttion is not run    End Sub   NOTE  this function in actual use recalculated a pivot table if a user added a item in a data range of A4 to  D500  The there were protected and unprotected sections in the sheet so the actual check for the click is if the column is less that  E  The logic can get as complex as you want to include or exclude any number of areas  block1    row  gt  3 and row  lt  5 and column column  gt  b  and  lt   d   block2    row  gt  7 and row  lt  12 and column column  gt  b  and  lt   d   block3    row  gt  10 and row  lt  15 and column column  gt  e  and  lt   g   If block1 or block2 or block 3 then   do function       end if

User · Answer

I don t think so  But you can create a shape object   or wordart or something similiar   hook Click event and place the object to position of the specified cell

User · Answer

In order to trap repeated clicks on the same cell  you need to move the focus to a different cell  so that each time you click  you are in fact moving the selection    The code below will select the top left cell visible on the screen  when you click on any cell  Obviously  it has the flaw that it won t trap a click on the top left cell  but that can be managed  eg by selecting the top right cell if the activecell is the top left    Private Sub Worksheet SelectionChange ByVal Target As Range     put your code here to process the selection  then     ActiveWindow VisibleRange Cells 1  1  Select End Sub

User · Answer

In order to trap repeated clicks on the same cell  you need to move the focus to a different cell  so that each time you click  you are in fact moving the selection    The code below will select the top left cell visible on the screen  when you click on any cell  Obviously  it has the flaw that it won t trap a click on the top left cell  but that can be managed  eg by selecting the top right cell if the activecell is the top left    Private Sub Worksheet SelectionChange ByVal Target As Range     put your code here to process the selection  then     ActiveWindow VisibleRange Cells 1  1  Select End Sub

User · Answer

I don t think so  But you can create a shape object   or wordart or something similiar   hook Click event and place the object to position of the specified cell

User · Answer

In order to trap repeated clicks on the same cell  you need to move the focus to a different cell  so that each time you click  you are in fact moving the selection    The code below will select the top left cell visible on the screen  when you click on any cell  Obviously  it has the flaw that it won t trap a click on the top left cell  but that can be managed  eg by selecting the top right cell if the activecell is the top left    Private Sub Worksheet SelectionChange ByVal Target As Range     put your code here to process the selection  then     ActiveWindow VisibleRange Cells 1  1  Select End Sub

User · Answer

SelectionChange is the event built into the Excel Object model for this  It should do exactly as you want  firing any time the user clicks anywhere       I m not sure that I understand your objections to global variables here  you would only need 1 if you use the Application SelectionChange event  However  you wouldn t need any if you utilize the Workbook class code behind  to trap the Workbook SelectionChange event  or the Worksheet class code behind  to trap the Worksheet SelectionChange  event   Unless your issue is the  global variable reset  problem in VBA  for which there is only one solution  error handling everywhere  Do not allow any unhandled errors  instead log them and or  soft-report  an error as a message box to the user    You might also need to trap the Worksheet Activate   and Worksheet Deactivate   events  or the equivalent in the Workbook class  and or the Workbook Activate and Workbook Deactivate   events so that you know when the user has switched worksheets and or workbooks  The Window activate and deactivate events should make this approach complete  They could all call the same exact procedure  however  they all denote the same thing  the user changed the  focus   if you will   If you don t like VBA  btw  you can do the same using VB NET or C     Edit  Dbb makes a very good point about the SelectionChange event not picking up a click when the user clicks within the currently selected cell  If you need to pick that up  then you would need to use subclassing

User · Answer

SelectionChange is the event built into the Excel Object model for this  It should do exactly as you want  firing any time the user clicks anywhere       I m not sure that I understand your objections to global variables here  you would only need 1 if you use the Application SelectionChange event  However  you wouldn t need any if you utilize the Workbook class code behind  to trap the Workbook SelectionChange event  or the Worksheet class code behind  to trap the Worksheet SelectionChange  event   Unless your issue is the  global variable reset  problem in VBA  for which there is only one solution  error handling everywhere  Do not allow any unhandled errors  instead log them and or  soft-report  an error as a message box to the user    You might also need to trap the Worksheet Activate   and Worksheet Deactivate   events  or the equivalent in the Workbook class  and or the Workbook Activate and Workbook Deactivate   events so that you know when the user has switched worksheets and or workbooks  The Window activate and deactivate events should make this approach complete  They could all call the same exact procedure  however  they all denote the same thing  the user changed the  focus   if you will   If you don t like VBA  btw  you can do the same using VB NET or C     Edit  Dbb makes a very good point about the SelectionChange event not picking up a click when the user clicks within the currently selected cell  If you need to pick that up  then you would need to use subclassing

User · Answer

SelectionChange is the event built into the Excel Object model for this  It should do exactly as you want  firing any time the user clicks anywhere       I m not sure that I understand your objections to global variables here  you would only need 1 if you use the Application SelectionChange event  However  you wouldn t need any if you utilize the Workbook class code behind  to trap the Workbook SelectionChange event  or the Worksheet class code behind  to trap the Worksheet SelectionChange  event   Unless your issue is the  global variable reset  problem in VBA  for which there is only one solution  error handling everywhere  Do not allow any unhandled errors  instead log them and or  soft-report  an error as a message box to the user    You might also need to trap the Worksheet Activate   and Worksheet Deactivate   events  or the equivalent in the Workbook class  and or the Workbook Activate and Workbook Deactivate   events so that you know when the user has switched worksheets and or workbooks  The Window activate and deactivate events should make this approach complete  They could all call the same exact procedure  however  they all denote the same thing  the user changed the  focus   if you will   If you don t like VBA  btw  you can do the same using VB NET or C     Edit  Dbb makes a very good point about the SelectionChange event not picking up a click when the user clicks within the currently selected cell  If you need to pick that up  then you would need to use subclassing

User · Answer

I don t think so  But you can create a shape object   or wordart or something similiar   hook Click event and place the object to position of the specified cell

User · Answer

This has worked for me       Private Sub Worksheet Change ByVal Target As Range       If Mid Target Address  3  1        And Mid Target Address  2  1   lt   E  Then          The logic in the if condition will filter for a specific cell or block of cells        Application ScreenUpdating   False         MsgBox  You just changed    amp  Target Address          all conditions are true      DO THE FUNCTION NEEDED         Application ScreenUpdating   True     End If       if clicked cell is not in the range then do nothing  if condttion is not run    End Sub   NOTE  this function in actual use recalculated a pivot table if a user added a item in a data range of A4 to  D500  The there were protected and unprotected sections in the sheet so the actual check for the click is if the column is less that  E  The logic can get as complex as you want to include or exclude any number of areas  block1    row  gt  3 and row  lt  5 and column column  gt  b  and  lt   d   block2    row  gt  7 and row  lt  12 and column column  gt  b  and  lt   d   block3    row  gt  10 and row  lt  15 and column column  gt  e  and  lt   g   If block1 or block2 or block 3 then   do function       end if

User · Answer

This has worked for me       Private Sub Worksheet Change ByVal Target As Range       If Mid Target Address  3  1        And Mid Target Address  2  1   lt   E  Then          The logic in the if condition will filter for a specific cell or block of cells        Application ScreenUpdating   False         MsgBox  You just changed    amp  Target Address          all conditions are true      DO THE FUNCTION NEEDED         Application ScreenUpdating   True     End If       if clicked cell is not in the range then do nothing  if condttion is not run    End Sub   NOTE  this function in actual use recalculated a pivot table if a user added a item in a data range of A4 to  D500  The there were protected and unprotected sections in the sheet so the actual check for the click is if the column is less that  E  The logic can get as complex as you want to include or exclude any number of areas  block1    row  gt  3 and row  lt  5 and column column  gt  b  and  lt   d   block2    row  gt  7 and row  lt  12 and column column  gt  b  and  lt   d   block3    row  gt  10 and row  lt  15 and column column  gt  e  and  lt   g   If block1 or block2 or block 3 then   do function       end if

User · Answer

I had a similar issue  and I fixed by running the macro  onTime   and by using some global variables to only run once the user has stopped clicking   Public macroIsOnQueue As Boolean Private Sub Worksheet SelectionChange ByVal Target As Range      macroIsOnQueue   False     Application OnTime  Now     TimeValue  00 00 02      addBordersOnRow      macroIsOnQueue   True End sub Sub addBordersOnRow       If macroIsOnQueue Then                macroIsOnQueue   False          add code here     End if End sub   This way  whenever the user changes selection within 2 seconds  the macroIsOnQueue variable is set to false  but the last time selection is changed  macroIsOnQueue is set to true  and the macro will run   Hope this helps  Have fun with VBA

User · Answer

I don t think so  But you can create a shape object   or wordart or something similiar   hook Click event and place the object to position of the specified cell

User · Answer

I had a similar issue  and I fixed by running the macro  onTime   and by using some global variables to only run once the user has stopped clicking   Public macroIsOnQueue As Boolean Private Sub Worksheet SelectionChange ByVal Target As Range      macroIsOnQueue   False     Application OnTime  Now     TimeValue  00 00 02      addBordersOnRow      macroIsOnQueue   True End sub Sub addBordersOnRow       If macroIsOnQueue Then                macroIsOnQueue   False          add code here     End if End sub   This way  whenever the user changes selection within 2 seconds  the macroIsOnQueue variable is set to false  but the last time selection is changed  macroIsOnQueue is set to true  and the macro will run   Hope this helps  Have fun with VBA

User · Answer

This has worked for me       Private Sub Worksheet Change ByVal Target As Range       If Mid Target Address  3  1        And Mid Target Address  2  1   lt   E  Then          The logic in the if condition will filter for a specific cell or block of cells        Application ScreenUpdating   False         MsgBox  You just changed    amp  Target Address          all conditions are true      DO THE FUNCTION NEEDED         Application ScreenUpdating   True     End If       if clicked cell is not in the range then do nothing  if condttion is not run    End Sub   NOTE  this function in actual use recalculated a pivot table if a user added a item in a data range of A4 to  D500  The there were protected and unprotected sections in the sheet so the actual check for the click is if the column is less that  E  The logic can get as complex as you want to include or exclude any number of areas  block1    row  gt  3 and row  lt  5 and column column  gt  b  and  lt   d   block2    row  gt  7 and row  lt  12 and column column  gt  b  and  lt   d   block3    row  gt  10 and row  lt  15 and column column  gt  e  and  lt   g   If block1 or block2 or block 3 then   do function       end if

User · Answer

Just a follow-up to dbb s accepted answer  Rather than adding the immediate cell on the right to the selection  why not select a cell way off the working range  i e  a dummy cell that you know the user will never need   In the following code cell ZZ1 is the dummy cell  Private Sub Worksheet SelectionChange ByVal Target As Range    Application EnableEvents   False   Union Target  Me Range  ZZ1    Select   Application EnableEvents   True      Respond to click selection-change here  End Sub

User · Answer

In order to trap repeated clicks on the same cell  you need to move the focus to a different cell  so that each time you click  you are in fact moving the selection    The code below will select the top left cell visible on the screen  when you click on any cell  Obviously  it has the flaw that it won t trap a click on the top left cell  but that can be managed  eg by selecting the top right cell if the activecell is the top left    Private Sub Worksheet SelectionChange ByVal Target As Range     put your code here to process the selection  then     ActiveWindow VisibleRange Cells 1  1  Select End Sub

User · Answer

SelectionChange is the event built into the Excel Object model for this  It should do exactly as you want  firing any time the user clicks anywhere       I m not sure that I understand your objections to global variables here  you would only need 1 if you use the Application SelectionChange event  However  you wouldn t need any if you utilize the Workbook class code behind  to trap the Workbook SelectionChange event  or the Worksheet class code behind  to trap the Worksheet SelectionChange  event   Unless your issue is the  global variable reset  problem in VBA  for which there is only one solution  error handling everywhere  Do not allow any unhandled errors  instead log them and or  soft-report  an error as a message box to the user    You might also need to trap the Worksheet Activate   and Worksheet Deactivate   events  or the equivalent in the Workbook class  and or the Workbook Activate and Workbook Deactivate   events so that you know when the user has switched worksheets and or workbooks  The Window activate and deactivate events should make this approach complete  They could all call the same exact procedure  however  they all denote the same thing  the user changed the  focus   if you will   If you don t like VBA  btw  you can do the same using VB NET or C     Edit  Dbb makes a very good point about the SelectionChange event not picking up a click when the user clicks within the currently selected cell  If you need to pick that up  then you would need to use subclassing

User · Answer

Just a follow-up to dbb s accepted answer  Rather than adding the immediate cell on the right to the selection  why not select a cell way off the working range  i e  a dummy cell that you know the user will never need   In the following code cell ZZ1 is the dummy cell  Private Sub Worksheet SelectionChange ByVal Target As Range    Application EnableEvents   False   Union Target  Me Range  ZZ1    Select   Application EnableEvents   True      Respond to click selection-change here  End Sub

[vba] OnClick in Excel VBA

Examples related to vba

Examples related to excel