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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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
24,220
The code in the OP directs DropDown2 to show its list. Try DropDown2.SetFocus instead. (untested)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,878
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top