Context-sensitive Actions pane: Odd update behavior

I have an Excel workbook that contains two named Ranges: OrderDate and ShipDate. When I select either of these cells, I want the Actions pane to display a user control that contains a label and a DateTimePicker control. I want the user control to show up in the Actions pane only when one of these cells is selected, so I have code on the ranges' Selected and Deselected event handlers to add or remove the control (see code below--SetDate is the name of the user control).

The solution works fine when I put my cursor on OrderDate and then move to ShipDate. It also works fine when I move from any other cell to either OrderDate or ShipDate.

The solution does NOT work when I move from ShipDate to OrderDate. The control is removed but not added. I added some message boxes and it looks like the events are firing in the wrong order: The OrderDate_Selected event fires BEFORE the ShipDate_Deselected event.

(In the OrderDate to ShipDate scenario the events fire as I would expect, OrderDate_Deselected and then ShipDate_Selected.)

What's up with that Anyone have a solution to suggest that isn't a hack

Just so you know, there's nothing special about the workbook. I was able to recreate on a completely vanilla new workbook/project. The code to show the Actions pane is in the ThisWorkbook_Startup handler. Here's the code on the sheet:

Public Class Sheet1
Private ucSetDate As New SetDate
Private Sub OrderDate_Deselected( _
ByVal Target As Microsoft.Office.Interop.Excel.Range) _
Handles OrderDate.Deselected
MessageBox.Show("OrderDate_Deselected")
Globals.ThisWorkbook.ActionsPane.Controls.Remove( _
ucSetDate)
End Sub

Private Sub OrderDate_Selected( _
ByVal Target As Microsoft.Office.Interop.Excel.Range) _
Handles OrderDate.Selected
MessageBox.Show("OrderDate_Selected")
Globals.ThisWorkbook.ActionsPane.Controls.Add( _
ucSetDate)
ucSetDate.lblPickDate.Text = "Choose order date"
End Sub

Private Sub ShipDate_Deselected( _
ByVal Target As Microsoft.Office.Interop.Excel.Range) _
Handles ShipDate.Deselected
MessageBox.Show("ShipDate_Deselected")
Globals.ThisWorkbook.ActionsPane.Controls.Remove( _
ucSetDate)
End Sub

Private Sub ShipDate_Selected( _
ByVal Target As Microsoft.Office.Interop.Excel.Range) _
Handles ShipDate.Selected
MessageBox.Show("ShipDate_Selected")
Globals.ThisWorkbook.ActionsPane.Controls.Add( _
ucSetDate)
ucSetDate.lblPickDate.Text = "Choose ship date"
End Sub
End Class

Answer this question

Context-sensitive Actions pane: Odd update behavior

silvarea
Actually, it kind of was the answer I was looking for. I needed some verification that the events were nondeterministic (although I didn't say that in my original message). Kathleen gave me an easy and not kludgy solution, and you both gave me the background I needed to explain it properly to readers/students. Thank you!
JohnsonInBeijing
>>Wouldn't something along those lines fix this problem

Well, not exactly. Both events (deselecting one range and selecting another) should technically fire. It's just that they sometimes fire in one order and sometimes the other (and by sometimes I don't mean it's random--it's consistent with a given control deselect/select order). I'll probably end up having to use some sort of flag, but it's going to be messy and this is a teaching example so I really want to know exactly why the behavior happens. Then I can explain it rather than just saying that the behavior is "quirky."

I really don't think this behavior is by design; if it were, wouldn't the order be either one way or the other all the time

I'll try the Excel newsgroup, but these are events on Excel Range objects that are exposed only to VSTO solutions. Also, my solution is pretty much the same as the one described in the Actions Pane chapter of the Carter/Lippert book, so it has at least the appearance of being "blessed" by the VSTO team.
legoman26
Works like a charm--a little screen flash (when both controls are in the pane at the same time) but acceptable.

Thanks Kathleen!
--jan
RogerAu

So I've not been able to make this behave non-deterministically but then my code (below) doesn't show UI within the events which may be altering the results. There's a lot going on under the hood. The delegates used by the events are stored in a hashtable, the events are actually coming from a COM object via interop etc.

All I can say is that in general is is bad practice to rely on the ordering of events (in VSTO we had to add special code to get the sheet startups to fire in a deterministic way) but in general you should not rely on event ordering in any .NET application.

Sorry this wasn't the answer you were looking for but I think it reflects what's really going on here.

Ade

private void Sheet1_Startup(object sender, System.EventArgs e)

{

Range2.Selected += new DocEvents_SelectionChangeEventHandler(Range2_Selected);

Range1.Selected += new DocEvents_SelectionChangeEventHandler(Range1_Selected);

Range1.Deselected += new DocEvents_SelectionChangeEventHandler(Range1_Deselected);

Range2.Deselected += new DocEvents_SelectionChangeEventHandler(Range2_Deselected);

}

void Range2_Deselected(Range Target)

{

Debug.WriteLine("Range2 deselected");

}

void Range2_Selected(Range Target)

{

Debug.WriteLine("Range2 selected");

}

void Range1_Deselected(Range Target)

{

Debug.WriteLine("Range1 deselected");

}

void Range1_Selected(Range Target)

{

Debug.WriteLine("Range1 selected");

}
cell-gfx
Hi Jan,

The order of events of multiple NamedRange controls is nondeterministic, and this is why the order isn't one way or the other all the time. The difference between your solution and the one in Eric & Eric's book is that you are using the same control for each NamedRange control. You can solve the problem you're encountering by creating two separate controls. This way it won't matter when the Selected event of one control (thus adding the control) is raised prior to the Deselected event of the other control (thus removing the same control you just added).

Private ucShipDate As New ShipDate
Private ucOrderDate As New ShipDate

Private Sub OrderDate_Deselected( _

ByVal Target As Microsoft.Office.Interop.Excel.Range) _

Handles OrderDate.Deselected

'MessageBox.Show("OrderDate_Deselected")

Globals.ThisWorkbook.ActionsPane.Controls.Remove( _

ucOrderDate)

End Sub

Private Sub OrderDate_Selected( _

ByVal Target As Microsoft.Office.Interop.Excel.Range) _

Handles OrderDate.Selected

'MessageBox.Show("OrderDate_Selected")

Globals.ThisWorkbook.ActionsPane.Controls.Add( _

ucOrderDate)

ucOrderDate.lblPickDate.Text = "Choose order date"

End Sub

Private Sub ShipDate_Deselected( _

ByVal Target As Microsoft.Office.Interop.Excel.Range) _

Handles ShipDate.Deselected

'MessageBox.Show("ShipDate_Deselected")

Globals.ThisWorkbook.ActionsPane.Controls.Remove( _

ucShipDate)

End Sub

Private Sub ShipDate_Selected( _

ByVal Target As Microsoft.Office.Interop.Excel.Range) _

Handles ShipDate.Selected

'MessageBox.Show("ShipDate_Selected")

Globals.ThisWorkbook.ActionsPane.Controls.Add( _

ucShipDate)

ucShipDate.lblPickDate.Text = "Choose ship date"

End Sub

Kathleen McGrath
http://blogs.msdn.com/vsto2/
jsin
I suspect that this is "by design" behavior on Excel's part. You might want to ask about it on the Excel programming newsgroup.

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US

I think it would be possible to write a workaround where your events maintained some sort of state so that the Selected and Deselected events are "paired". So that the deselected event from one range doesn't do anything if the actionspane control was shown in response to a select event from a different range. Wouldn't something along those lines fix this problem

Thanks,

Ade
Context-sensitive Actions pane: Odd update behavior

Answer this question