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: 18787
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: 18787
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: 18787
Joined: 2006-05-11
|
|
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: 18787
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
|