problem moving focus combobox to another

Scoobidoo

New Member
Joined
Sep 29, 2014
Messages
7
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:

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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
What error does it throw?

Notwithstanding, try using the AfterUpdate event rather than Change.

Welcome to the board btw :)

/AJ
 

Scoobidoo

New Member
Joined
Sep 29, 2014
Messages
7
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?
 
Last edited:

Scoobidoo

New Member
Joined
Sep 29, 2014
Messages
7
@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

New Member
Joined
Sep 29, 2014
Messages
7

ADVERTISEMENT

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?
 

Scoobidoo

New Member
Joined
Sep 29, 2014
Messages
7

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I'd give up with the idea if I were you. ActiveX controls can be unpredictable when used on a worksheet.
 

Scoobidoo

New Member
Joined
Sep 29, 2014
Messages
7
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 ...
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
The code in the OP directs DropDown2 to show its list. Try DropDown2.SetFocus instead. (untested)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,711
Messages
5,524,436
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top