problem moving focus combobox to another

[Scoobidoo]

Hi,

I've about 140 comboboxes and a user should choose a value in each combobox.
In order to make input easier I'd like to drop-down automatically the next combobox after the user has chosen a value in the previous one.
For a start I tried it with 2 comboboxes but Excel crashes always when trying to move the focus.

Does anyone have an idea how to drop-down automatically the next combobox?
My not working code:

Private Sub Worksheet_Activate()
     Me.ComboBox1.DropDown
End Sub

  
Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then
          Me.ComboBox2.DropDown
    End If
End Sub

 

[adam087]

What error does it throw?

Notwithstanding, try using the AfterUpdate event rather than Change.

Welcome to the board btw

/AJ

 

[Scoobidoo]

Wow, thanks for the quick reply.

There is no error shown. Excel hangs for a few seconds and then a message that Excel isn't working anymore. Then, Excel restarts correctly.

I've been looking for the "afterupdate"-property but cannot find it. I know, in Access, there is this property. What do I have to do to enable the property in Excel?

 

[Scoobidoo]

@AJ,

Could it be useful to know that I work with a Dutch version of Excel 2007.
In the intellisense I can "see" all the typed code.

Would you mind to test it in an English version of Excel? You just have to put 2 comboboxes on a new sheet and copy my code.
Can you reproduce my problem?

 

[Scoobidoo]

Anyone else who wants to help or who has the same issue??


Just take a new sheet and add 2 comboboxes (activeX) on the sheet. Paste my code in the vba editor of that sheet and see if Excel crashes.

Please, let me know what your result is?

 

[Andrew Poulsom]

In Excel 2010 it doesn't cause a crash, but nothing happens.

 

[Scoobidoo]

Mr Poulsom,

I'm very pleased to have a reaction.
Thank you for taking time to test.

Now that I know it isn't a problem on my pc, do you have a suggestion for a work-around?

 

[Andrew Poulsom]

I'd give up with the idea if I were you. ActiveX controls can be unpredictable when used on a worksheet.

 

[Scoobidoo]

Mr Poulsom,

Thanks again for the reply.
This isn't the answer that I was hoping for but it is still an answer, even more, it is a clear one.

Hoped I could use Excel for questioning young people for their activities in spare time.

Apparently, Excel isn't the right tool to do so. Too bad.

If you can give a hint to an Excel developper ...

 

[mikerickson]

The code in the OP directs DropDown2 to show its list. Try DropDown2.SetFocus instead. (untested)