Gen. Question about updating VB Cell Ranges

[palmese]

I have a general VB question that I was hoping someone can shed light on.

I have developed an elementary expense report for my company using Excel as the interface and some VB code. The excel interfaces includes a number of comboboxes with user selections. An example of the code is as follows:

Private Sub ComboBox1_Change()

resetAllChildDropDowns1

If ComboBox1.Value = "Asia Buyout" Then
ComboBox2.ListFillRange = "g73:g73"
ElseIf ComboBox1.Value = "Asia Venture Fund" Then
ComboBox2.ListFillRange = "g74:g75"
ElseIf ComboBox1.Value = "CAMG-Fund of Funds" Then
ComboBox2.ListFillRange = "g76:g76"
ElseIf ComboBox1.Value = "CAMG-Secondary Fund" Then
ComboBox2.ListFillRange = "g77:g77"
ElseIf ComboBox1.Value = "CMG Fund" Then
ComboBox2.ListFillRange = "g78:g96"
ElseIf ComboBox1.Value = "Energy Fund" Then
ComboBox2.ListFillRange = "g97:g99"
ElseIf ComboBox1.Value = "Europe Buyout" Then
ComboBox2.ListFillRange = "g99:g99"
ElseIf ComboBox1.Value = "Europe Real Estate" Then
ComboBox2.ListFillRange = "g101:g101"
ElseIf ComboBox1.Value = "Europe Venture" Then
ComboBox2.ListFillRange = "g102:g102"
ElseIf ComboBox1.Value = "Japan Buyout" Then
ComboBox2.ListFillRange = "g103:g103"
ElseIf ComboBox1.Value = "Japan Venture" Then
ComboBox2.ListFillRange = "g104:g104"
ElseIf ComboBox1.Value = "US Buyout" Then
ComboBox2.ListFillRange = "g105:g239"
ElseIf ComboBox1.Value = "US Real Estate" Then
ComboBox2.ListFillRange = "g240:g312"
ElseIf ComboBox1.Value = "US Venture Fund I" Then
ComboBox2.ListFillRange = "g313:g330"
ElseIf ComboBox1.Value = "US Venture Fund II" Then
ComboBox2.ListFillRange = "g331:g342"
End If

ComboBox2.Enabled = True

End Sub

My problem is that I am getting requests to add new projects to the expense reports on a daily basis. In order to do so I add the project name, accting code and some other stuff to the Excel spreadsheet and then have to make manual HARDCODED changes to the VB code in order to change the range in the Combobox.

Any suggestion would be appreciated. I am getting the feeling that VB is somewhat limiting in what I need to do and that Access would be the only way to go. Thanks.

 

[CajunCenturion]

Set up a table external to the program with three columns. The first column contains the description, the second column contains the first cell in the range, and the third column is the ending range. There are lots of ways to actually implment this, but for the purposes of this example, assume that you have loaded this into a 2 dimensional array called TableMap which is loaded when the program first starts up, then your change event code would be the following:

Private Sub ComboBox1_Change()

   Dim Idx as Integer

   resetAllChildDropDowns1

   For Idx = 0 to UBound(TableMap)
      If ComboBox1.Value = TableMap(Idx, 0) Then
         ComboBox2.ListFillRange = TableMap(Idx, 1) & ":" & TableMap(Idx, 2)
         Exit For
      End If
   Next Idx

    ComboBox2.Enabled = True
    
End Sub

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[palmese]

CajunCenturion, Thanks so much! That makes a lot of sense. My follow up question would be how should define/implement the 2 dimensional array called TableMap. I don't have a lot of programming experience. I understand the concept, I'm just not sure how to implement it in VB. And thoughts/examples you might have would be greatly appreciated.

 

[CajunCenturion]

For starters, lets just define the array, after that, I'll leave it up to you to move it into a text file, or a database table. Remember the array is 0 based so the following will be for 6 entries.

Dim TableMap(5, 2)
TableMap(0,0)="Asia Buyout":TableMap(0,1)="g73":TableMap(0,2)="g73"
TableMap(1,0)="CAMG-Fund of Funds":TableMap(1,1)="g74":TableMap(1,2)="g75"
TableMap(2,0)="CAMG-Secondary Fund":TableMap(2,1)="g76":TableMap(2,2)="g76"

and so forth - you get the idea. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Left off the data type for the array.

Dim TableMap(5, 2) As String
TableMap(0,0)="Asia Buyout":TableMap(0,1)="g73":TableMap(0,2)="g73"
TableMap(1,0)="CAMG-Fund of Funds":TableMap(1,1)="g74":TableMap(1,2)="g75"
TableMap(2,0)="CAMG-Secondary Fund":TableMap(2,1)="g76":TableMap(2,2)="g76" Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HobbitK]

Palmese ...
Instead of using all those If ... ElseIf statements, I would strongly consider using a SelectCase Statement.
your code will be quicker and much easier to read/maintain/add to or delete from.
Set up the array and tables just like Cajun recommends.