Filling drop down box when reloading ribbon

Hi

I have a customised menu item for an application on my Excel ribbon. This item has a number of fixed drop-down boxes from which users make a selection. One of these is filled from values entered by the user on another sheet, so cannot be hard coded into the ribbon set-up. When the selection is made the selected item is displayed in the drop-down box window.

When the user selects a certain column, a new menu drop down box are displayed relevant to the column selected. The user then selects from that drop-down and the selected value is inserted in the relevant cell in the sheet. As the user moves from column to column the ribbon is reloaded to display the relevant drop-down box.

The problem is that when reloading the ribbon, the value displayed in the fixed drop-down box is lost. The values are still available in the drop-down but the value in use is no longer visible - the drop-down window appears blank. I would like to be able to display the in-use (selected) value in the drop-down whenever the ribbon is reloaded.

Any advice would be appreciated.

Mick
PS I've tried to insert snipped images to aid understanding but when I click the Insert image icon I receive the insert image dialog but cannot browse to select the image.

Hi Andy

Thanks for the reply. The file is 7 Mb so I'll chop it down, leaving the relevant sections that illustrate my issue and then send.

Mick

Hi again

I've attached a cut-down file but, apart from a run time error when you select the ProCost item on the ribbon, seems to work OK.

With any of the blue highlighted cells in column D selected, you can select from the Select Input Currency drop-down and the selected value stays in the window of the drop-down. You can alternate between them without a problem.

However, when selecting say H10, the Select Component drop-down appears to enable data entry into the cell from the drop-down. The same principle applies for column J. The problem is that because the ribbon is reloaded when columns H or J are selected, the window of the Select Input Currency drop-down is blank. I would like to be able to retain (or redisplay) the currently selected (in use) currency.

Again, any advice would be appreciated.

I should add that I am not a VBA programmer (as will probably be obvious) but use Excel quite extensively in work so have taught myself (with advice from forums such as this) to produce this sort of applications.

Mick
PS I'm 5 hours ahead of BST so if I do not acknowledge any posts it's because I'm asleep not because I'm ignoring them!