Excel cell selection

Add-in Express™ Support Service
That's what is more important than anything else

Excel cell selection
problems of cell collection with "protect" 
Posts 1 - 10 of 12
First | Prev. | 1 2 | Next | Last 
Michael Kaden


Guest


Dear Andrei,

I have a problem with the following code in the Com AddIn:

Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlBook = alera.AddinModule.CurrentInstance.GetActiveWorkbook()
xlSheet = alera.AddinModule.CurrentInstance.GetActiveSheet()
If xlBook IsNot Nothing Then
Dim wSheets As Excel.Sheets = xlBook.Worksheets
For iSheet As Integer = 1 To wSheets.Count
Dim wks As Excel._Worksheet = CType(wSheets.Item(iSheet), Excel._Worksheet)
wks.Unprotect()
wks.Protect()
Marshal.ReleaseComObject(wks) : wks = Nothing
Next
End If

Marshal.ReleaseComObject(xlBook) : xlBook = Nothing
Marshal.ReleaseComObject(xlSheet) : xlSheet = Nothing


The problem accurse only when I have the "wks.protect()" line in the code. After the code is executed, the previous cell in the sheet is selected say "A1". When I select another cell, say "B1" then "A1" and "B1" is selected and when I click on "A1" then all cells are deselected. If I click another cell again, for example "C1" then "A1" and "C1" are selected. The problem of this selection goes away, when I select another worksheet and come back to the original worksheet. If I add the following to the cede, the problem still exists:

For iSheet As Integer = wSheets.Count To 1
Dim wk2 As Excel._Worksheet = CType(wSheets.Item(iSheet), Excel._Worksheet)
wk2.Activate()
'Marshal.ReleaseComObject(wks) : wks = Nothing
Next


Any idea why this is happening and how to prevent it?

Thank you for your support and kind regards

Michael
Posted 07 Feb, 2018 08:08:33 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

In what event do you execute this code fragment? Does the issue occur if you execute it in the Click event of a Ribbon button?


Andrei Smolin
Add-in Express Team Leader
Posted 07 Feb, 2018 08:21:45 Top
Michael Kaden


Guest


Dear Andrei,

thank you very much for the prompt answer. Yes the issue is reproducible with a ribbon click.
I made a small sample project which I send the support email.

Thank you and regards

Michael
Posted 07 Feb, 2018 10:55:33 Top
Michael Kaden


Guest


Ps you have to click

cellsel/Test/TestSelect 2 times before the double selection happens.

regards

Michael
Posted 07 Feb, 2018 11:06:30 Top
Michael Kaden


Guest


PS 2

If I use


wks.Protect(DrawingObjects:=False, Contents:=False, Scenarios:=False)

(all 3 entries are needed)

then the problem goes away, however this defeats the object of protection.

kind regards

Michael
Posted 07 Feb, 2018 11:48:20 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

I can't reproduce the issue. I start Excel, select A1 in the first sheet of the Book1 workbook, press the Ribbon button twice, select B1 and I see no issues you described.

1. Do I understand the scenario correctly?
2. This may relate to the Excel build that you use; I use Excel 2016 build 9026.2006. This is Office Insider.
3. Check if your keyboard requires replacement: I suppose Ctrl keys on it may not work correctly sometimes.

P.S. I'd also release wSheets in that method.


Andrei Smolin
Add-in Express Team Leader
Posted 08 Feb, 2018 01:30:31 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your comments. This gave me some ideas what to try.

I work with Office Professional 14.0.7015.1000
I use Office 2010 deliberately as upward compatibility to 2016 is more reliable than downwards from 2016 to 2010.

A bit more specific on the issue.

I open a new(unprotected) workbook with 3 (my standard) sheets

A1 is selected automatically

I press "Test select" - A1 stays selected

now all 3 sheets are protected (as expected)- I check on "Review Ribbon"

I press "Test select" again - the selection of A1 goes away - like if I would click outside of Excel although on a click within Excel the selection should not go away!

If I click the screen outside of Excel then the selection A1 comes back
I can repeat this = click on Excel when A1 is selected, then no selection shows up, click outside, then the selection A1 comes back

So the showing of the selected cell seems to be reversed, click outside of Excel the selection shows, click on the sheet, then the selection disappears.

If I click on Excel when nothing shows selected, then A1 and the cell where I clicked shows as selected
If I then click any other cell, then always A1 and the other cell shows as selected.

I can stop this behaviour if I select another sheet.

it looks like the initial selection ("A1") is locked as additional selection until I change sheets.

I tried to drill down the issue.

The problem is generated on the second round, i.e. in the first time protected sheets are there.
On the second round protected sheets are there and the A1 selection disappears when the second sheet (which is the first non active sheet) goes into the unprotect/protect sequence.

If I put a delay (> 1 sec)between the unprotect and protect commands, the problem goes away.
If I manually unprotect the sheets before pressing TestSelect again, the problem goes away.
If I manually protect the sheets before pressing TestSelect the problem accrues on the first time TestSelect click.

So it looks like the problem is based on the following prerequisites.

Unprotect a protected worksheet and immediately protect it again if the worksheet is not the active worksheet.

I can solve the issue if I add "wks.Activate()" in the code


Dim wks As Excel._Worksheet = CType(wSheets.Item(iSheet), Excel._Worksheet)
wks.Activate()
wks.Unprotect()
wks.Protect()
Marshal.ReleaseComObject(wks) : wks = Nothing
Next


To get back to the original sheet I add directly after the NEXT above:

Dim wkb As Excel._Worksheet = CType(wSheets.Item(1), Excel._Worksheet)
wkb.Activate()
Marshal.ReleaseComObject(wkb) : wkb = Nothing


this works fine so far, but I would really like to understand where the problem came from.

So there seems to be a problem to unprotect and protect sheets without delay if the sheets are not the active worksheets?

If you have any explanation for this, I would be grateful.

BTW I checked my Office installation (repair) and the keyboard, no problem found.

Thank you for your help & regards

Michael.
Posted 08 Feb, 2018 10:13:26 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

I've reproduced this issue on Excel 2010 14.0.7155.5000 SP2 32bit. It seemingly belongs to Excel. I suggest that you introduce a delay between calling Unprotect and Protect; use the SendMessage() method and the OnSendMessage event of the add-in module; check section Wait a little at https://www.add-in-express.com/docs/net-office-tips.php#wait-a-little.

In fact, I've tested introducing such a delay using the System.Windows.Forms.Application.DoEvents() method. But you should never use this method in a real-life add-in; instead, use the approach above or create a delay using a System.windows.Forms.Timer.

The below is put here for further reference :)

===
In fact, using DoEvents is not recommended. It may be acceptable in a standalone WinForms application which behavior is determined completely by your code and the code of the components that you use. In an Office extension, where a lot of code is executed outside of your code, DoEvents may cause many, many problems.

http://www.codinghorror.com/blog/2004/12/is-doevents-evil.html
http://www.codinghorror.com/blog/2005/08/is-doevents-evil-revisited.html
http://stackoverflow.com/questions/5181777/use-of-application-doevents on stackoverflowhttp://www.dacris.com/blog/2009/11/18/doevents-really-is-evil/
http://stackoverflow.com/questions/11352301/how-to-use-doevents-without-being-evil on stackoverflow
http://blogs.msdn.com/b/jfoscoding/archive/2005/08/06/448560.aspx ?Â?Ð?ã suggests ways out in typical scenarios
===


Andrei Smolin
Add-in Express Team Leader
Posted 09 Feb, 2018 05:36:19 Top
Michael Kaden


Guest


Dear Andrei,

thank you for the advise. I am never quite sure to be happy or not when finally the problem boils down to a MS bug.

Since the time I started to create VB6 stand alone applications some 20 years ago I avoided DoEvents() and also timers/wait/sleep in my code. Too many possibilities that the program does not come out of it.

With regard to the issue here, my experience is that the problem only exists, when ALL of the following is the case:

The worksheet is protected
The worksheet is NOT the active worksheet
Unprotect & protect follow quickly behind each other.

I tried many scenarios and with activating the worksheet before protect/unprotect the problem does not come up. So instead of introducing a time delay I would rather use wks.activate.

Can you see any reason why not to do it in this way?

Thank you and kind regards

Michael
Posted 09 Feb, 2018 06:10:59 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Michael,

Does using Activate solve the issue? If not, I assume that the Activate method doesn't create a delay when calling in this way.


Andrei Smolin
Add-in Express Team Leader
Posted 09 Feb, 2018 06:33:55 Top

Posts 1 - 10 of 12
First | Prev. | 1 2 | Next | Last