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 · 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

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

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

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

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 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

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

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

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

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

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

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

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

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

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

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

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

[vba] OnClick in Excel VBA

Examples related to vba

Examples related to excel