Unusual Behaviour Following Validation List Selection

[MartinS]

Hi
An odd issue, that only seems to happen the first time you try and as long as the first time shows the lower options - once you move away from the cell and then re-try it works as expected. Unusual Behaviour Following Validation List Selection

So I have a series of Yes/No validation list cells, and depending on the response, the code will hide or show relevant rows lower down the spreadsheet, as well as further rows of these Yes/No responses. Without being able to attach images to my post (I am unable to upload images offsite to link to due to work restrictions), I shall try and explain what is happening.
So the 'default' position is that there are two rows of Yes/No selection visible. The first is set to Yes, the second No.
When the user switches the No to Yes, three rows directly below appear, each again with further Yes/No requests. The selected cell remains the same, i.e. the cell switched from No to Yes, but the dropdown indicator (to the right of the cell) jumps to the last row in the group. Whichever of the three cells are clicked on, the same cell (the one set to Yes) stays selected i.e. surrounded by a green border, the options Yes/No appear below the bottom cell but the response is stored in the selected cell.
If I then swap sheets, or click away from these cells, and then click back and repeat, it works as expected.
Has anyone seen anything similar? FYI the worksheet is protected, and the code to unprotect/protect is called during the show/hide routine.
Many thanks
Martin

 

[MartinS]

Without the ability to add attachments, this is difficult to explain clearly, but it's a real problem as it is hindering use of the spreadsheet. I've never seen it before in all my years of Excel development.
Has anyone seen similar, or know how I can attach images (I'm sure I have been able to previously, and it's not like i'm a new user?!
Thanks
Martin

 

[RoryA]

Does the same thing happen if the sheet is not protected? Sounds like a bug to me.

 

[MartinS]

Does the same thing happen if the sheet is not protected? Sounds like a bug to me.

Funny enough, as soon as the worksheet is unprotected, focus reverts to the correct row, so yes, I'd agree. Apart from keeping the sheet unprotected, nothing I can do I guess?
Thanks for your reply.
Regards
Martin

 

[RoryA]

Have you tried protecting on workbook_open with the userinterfaceonly option so that the code doesn't have to unprotect and reprotect?

 

[MartinS]

Have you tried protecting on workbook_open with the userinterfaceonly option so that the code doesn't have to unprotect and reprotect?

No, that's not currently the way it works, so definitely worth trying it that way to see if that makes a difference.
Many thanks.
Martin

 

[MartinS]

No, that's not currently the way it works, so definitely worth trying it that way to see if that makes a difference.
Many thanks.
Martin

Sadly, no difference. If the sheet remains unprotected, it works without issue.

 

[MartinS]

We found a work-around, by selecting (in code) cell A1 of the sheet in question, then the cell that was last changed, and this seems to be sufficient to trigger Excel to display correctly.
Thanks for your help
Regards
Martin