Sleeper: Validation drop-down arrows not on active cell!

[The Tamer]

Hi guys,

I have an irritating side effect on one of my macros.

I have saved some custom views in my sheet - they show or hide columns depending on what the user has input in Column E on the last record. The custom view puts the active cell where it was when the view was set. So I have the following Macro on the Worksheet_Calculate doo-da:

Private Sub Worksheet_Calculate() 
On Error Resume Next 
Set UserCell = ActiveCell 
ThisWorkbook.CustomViews(Me.Range("AA1").Value).Show 
UserCell.Activate 
End Sub

Problem is, though the cell the user was on is re-selected, the validation dropdown arrow is mysteriously stuck on the cell that the CustomView selected.

Huh? It's not major - but can anyone help? (Excel 97)

Cheers

[Jacob Hilderbrand]

Why do you need to re-activate the UserCell? Is another cell being selected somewhere?

[The Tamer]

Why do you need to re-activate the UserCell? Is another cell being selected somewhere?

Yes, I've saved a custom view - so when the macro switches the current view to the custom one, the active cell becomes the one that was active when the view was first created. But I need the active cell to be the one the user is using.

[The Tamer]

Hmmm! Tried this out on Excel2003 at home, and I don't have the same problem.

I think that's another reason why this http://www.vbaexpress.com/forum/showthread.php?t=1796 is a good idea.

Does anyone at leat know how to switch the dropdown arrow off so that it doesn't confuse the user?

Admit it! You're stumped!