2

I need to intercept the event of multiple sheet selection in Excel.

A. Workbook_SheetActivate

I have used the Workbook_SheetActivate, but my issue is:

---> When the active sheet remained selected among a multiple selection, the event is not triggered! And I cannot list them in my DataGridView.

B. Workbook_SheetSelectionChange

I have used the Workbook_SheetSelectionChange, but my issue is:

---> It works only when the user select a cell in one of the Workseets. And the List of Selected Sheets should be updated as soon as the user change selections (Single or multiple selections)

Is there a way for me to intercept the list of selected Sheets even if I activate a sheet among the selection?

Tsiry Rakotonirina
  • 731
  • 4
  • 13
  • 2
    This seems to be an [x-y problem](http://xyproblem.info/). So, maybe [this](https://www.extendoffice.com/documents/excel/4166-excel-prevent-multiple-sheet-selection.html) helps or you change your post in order to reflect your real problem. – Storax May 24 '20 at 12:31
  • @Storax, thanks for the **x-y problem** approach, but I know my X Problem very clearly. I don't want to prevent users to select multiple sheets but rather intercept any selected sheets from a Workbook (Including multiple Sheets) and list them in a DataGridView in my Addins. As I explained, there are 2 events I can use but both fail when the active sheet is among the multiple selected sheet. So, in clear **how to intercept and list selected multiple sheets in an Open Workbook**. Thanks – Tsiry Rakotonirina May 24 '20 at 13:27
  • 1
    The selected sheets will be the [`SelectedSheets`](https://learn.microsoft.com/en-us/office/vba/api/excel.window.selectedsheets) property of the [`ActiveWindow`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.activewindow), but I'm not sure how you'd trigger an event when that changes... – Chronocidal May 24 '20 at 13:38
  • 1
    Ok, as Chronocida stated and as you also find in my second link you will find the selected sheets in `ActiveWindow.SelectedSheets` but also as Chronocida I am not aware of any event that fires in case you select more than one sheet. – Storax May 24 '20 at 14:25
  • Thanks to both of you Chronocidal and Storax, that's exactly my struggle as I try to build a List of Sheets like in used by Kutools, but to reflect the sheet selections on my Addins. I keep searching and if either any of us find a solution then surely we will win together! – Tsiry Rakotonirina May 24 '20 at 14:38
  • Maybe [this](https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell) is an approach to follow. You would need to add code for mouse "strokes" as well. And you would need to add code to check if more than one sheet has been selected. – Storax May 24 '20 at 15:16
  • 1
    You could use a loop with OnTime (on a short-ish period of a couple of seconds or so) to monitor the sheet selection. – Tim Williams May 24 '20 at 16:50
  • @TimWilliams wouldn't `Workbook_SheetSelectionChange` even be better than `OnTime`? With `OnTime` excel will keep checking every few seconds and hence, I think its not more effiiicent than `Workbook_SheetSelectionChange` where it will check only on change of range selection. But that happens after multiple selection of sheets. OP is looking for the exact event of selection of sheets (more than one). I don't know if there is such. I wonder can we have such custom event created? ..once, such an event is captured then this would be useful https://stackoverflow.com/a/8801993/9808063 – Naresh May 24 '20 at 17:41

0 Answers0