I have found using Excel’s built in RefEdit crashtastic, especially when a user’s machine has had multiple versions of Office installed, and upgraded.
To get around all the errors including “Error -2147467259 (&H80004005) : “Unspecified error”, I have found that re-installation of Office, or running an Office repair will fix the issue.
Ain’t nobody got time for that, so I created a class to manually create a custom RefEdit control alternative. The class hosts a textbox control with it’s Drop Button Style set. A formula type input box capture’s the actual range selection.
The class hosts 4 events :
DropButtonClickStart – The user has clicked the refedit’s drop down
DropButtonClickEnd – The user has clicked the refedit’s OK button
Cancel – The user clicked the refedit’s Cancel button
ValueChange – the user has modified the refedit’s value