Reloading Data into a ComboBox using RowSource

[Alon]

Hi,

I have two comboboxes on a VBA form that use dynamic range names for
their RowSource data. Esentially when you select something from the
first combo box, I have it set a cell to the selected value using
controlsource which in turn changes a list on the sheet that was used
in the row source for the second combo box. I have two problems here:

1. When selecting a new item in the first combo box the value in my
excel sheet only updates when I take focus away from the combo box.
This gives the function a very non-dynamic feel.

2. The data from the rowsource does not refresh for the second combo
box. I tried setting row source again in the change event of the combo
box but due to my first issue this does not work since each time I
change something I have to take away focus so that the data updates in
excel, and then change it again so that it reloads the data. When I do
that it leads to a run-time error.

The only thing that comes to mind is to build two separate functions
that will perform the same operations that rowsource/controlsource do
but do it the way I want them to. I want to avoid doing this and use
what is available in excel without complicating the code even more.

Any suggestions?

Thanks,
Alon

 

[Alon]

PS.. To give you some more technical information. I have the following
code in the form:

***********
Private Sub cmbDB_Change()
cmbTV.Value = ""
End Sub

Private Sub cmbSrv_Change()
cmbDB.Value = ""
cmbDB.RowSource = "OFFSET(Misc!$F$2,0,0,MATCH(""*"",Misc!$F$2:$F
$40,-1),1)"
End Sub
***********

And the following data in the sheet if 'serv1' is selected in the
first box and 'db12' then selected in the second:

Controls Servers Databases
Server: serv1 serv1 db11
Database: db12 serv2 db12
Table: serv3 db13
db14

And the following data in the sheet if 'serv2' is selected in the
first box and 'db21' then selected in the second:

Controls Servers Databases
Server: serv2 serv1 db21
Database: db21 serv2 db22
Table: serv3


Notice that the database list changed based on the value for server.