After Update

[Guest]

I need to be able to refresh a second combo box (from the Control Toolbox)
based on the selection of the first combo box. I have seen many posts that
say to Code an AFTER UPDATE event for combo1 with a requery:
Me!Combo2.Requery
Where do I find this AFTER UPDATE? I am using Excel 2000
It is not listed in the declarations in VBA. I am not strong in VBA, just
getting started.

Thanks,

Squeaky

 

[Guest]

You can use the lost focus event. This event fires any time you leave the
combo box.

 

[Dave Peterson]

This doesn't look like Excel's VBA. Are you sure you didn't search in an Access
newsgroup?

If you really meant excel, maybe...
me.combo2.clear
'to clear the list???
then code to add back whatever you need -- maybe using .additem????

 

[Guest]

Either it won't work or I need more detail on how to set it up.
In design mode, I right click on the combobox and select view code. I see:

Private Sub ComboBox1_Change()

End Sub

Where do I put in the commands to refresh or reset? If I put it like this I
get an error:
Private Sub ComboBox1_Change()
Me!ComboBox2.Requery
End Sub

I tried the Lost Focus command and I can't get that to work either. I don't
know if I'm setting it up correctly or what.

More help?
Thanks,

Squeaky

 

[Dave Peterson]

This still doesn't look like Excel to me.

You sure you shouldn't be asking in an Access group?

 

[Guest]

Hi Dave,

I did a search on refreshing a combo box in the Excel Header group, not the
sub-group. It brought me up links to all sorts of places so I'm not sure if
what I have gathered is even pertinent. SO...
To start over this is what I have. I am Using EXCEL 2000. (Definitely NOT
Access). My spreadsheet has 3 combo boxes from the Control Toolbox. Box 1
selects certain companies we do business with. After selecting a business I
go to Combo box 2 and select an Item in stock that will display there. Once I
select the item, I go to combo box 3 and select the year I want to do
research on. After all 3 are selected the information I need will display on
the spreadsheet. If at this time if I go and select a new business, the other
two combo boxes will display whatever was in the row of the previous
business. If the previous business had more items, and if I had selected an
item that was way down the list, the re-selected combo box2 could be blank. I
have to select the combobox2, rescroll to the top, and make my selection. I
simply want the combo boxes that are "downstream" to refresh themselves when
a new business is selected.

Thanks!

Squeaky

 

[Dave Peterson]

There is no .requery in excel's vba for comboboxes.

Depending on what you mean by refresh:

me.combobox2.listindex = -1

It'll clear the choice from the combobox.

Hi Dave,

I did a search on refreshing a combo box in the Excel Header group, not the
sub-group. It brought me up links to all sorts of places so I'm not sure if
what I have gathered is even pertinent. SO...
To start over this is what I have. I am Using EXCEL 2000. (Definitely NOT
Access). My spreadsheet has 3 combo boxes from the Control Toolbox. Box 1
selects certain companies we do business with. After selecting a business I
go to Combo box 2 and select an Item in stock that will display there. Once I
select the item, I go to combo box 3 and select the year I want to do
research on. After all 3 are selected the information I need will display on
the spreadsheet. If at this time if I go and select a new business, the other
two combo boxes will display whatever was in the row of the previous
business. If the previous business had more items, and if I had selected an
item that was way down the list, the re-selected combo box2 could be blank. I
have to select the combobox2, rescroll to the top, and make my selection. I
simply want the combo boxes that are "downstream" to refresh themselves when
a new business is selected.

Thanks!

Squeaky

 

[Guest]

I don't really care whether it is VBA or something else. Whatever works.

Your code works, Thanks for the trouble.

Is there a setting that will auto select the first item in the list?

Thanks

Squeaky

 

[Dave Peterson]

I'm not sure what auto select means, but you can choose the first item in code:

me.combobox2.listindex = 0

0 is the first, 1 is the second, ...

I don't really care whether it is VBA or something else. Whatever works.

Your code works, Thanks for the trouble.

Is there a setting that will auto select the first item in the list?

Thanks

Squeaky