How do I maintain List Box values

[DBD]

Hi, I 'm a little new to excel.

Summary: I need help looping through values in a range of cell when I open a workbook to poulate a list box.

I have two combo boxes on one sheet. The change event of listbox1 queries a db base and returns a list of branches into another worksheet ("Sheet2") . I then loop the the returned values an populate listbox2. This works fine , except when I re-open the workbook the listbox2 is not populated. I've tried to call my sub it Workbook_open, but I an error.

"Comple Error in hidden Module: ThisWorkbook"

I have Listbox1 that I populate on workbook_Open
example:

    Worksheets("Sheet1").Listbox1.AddItem "Alanta"
    Worksheets("Sheet1").Listbox1.AddItem "Boston"
    Worksheets("Sheet1").Listbox1.AddItem "New York"

  call LoadTeamcenter ("Sheet2")' I get an error here

' here is sub that is in Sheet 1 module. So maybe my question should be How can i force the following sub on open.

Public Sub LoadTeamcenter(strWorksheetFrom As String)

  Dim ws As Worksheet
  
  Set ws = Worksheets(strWorksheetFrom)
'get number of Branches reteuned
intTeamcenterCount = GetRowCnt("Sheet2", "A", 2)


ReDim ArrTeamcenterNum(intTeamcenterCount)
ReDim ArrTeamcenterDesc(intTeamcenterCount)

r = 2
For i = 1 To intTeamcenterCount
               
          ArrTeamcenterNum(i) = ws.Cells(r, 1)
          ArrTeamcenterDesc(i) = ws.Cells(r, 2)
           
       r = r + 1
       Next
  
   Worksheets("Sheet1").cboTeamcenter.Clear
   
 For i = 0 To UBound(ArrTeamcenterDesc)
 
   Worksheets("Sheet1").cboTeamcenter.AddItem ArrTeamcenterDesc(i)
 

Next i
  
End Sub

Thanks in advance
DBD

 

[toe222]

that is exactly why im trying to get mine to do.

 

[DBD]

I posted my solution here
http://www.mrexcel.com/board2/viewtopic.php?t=129384