combo box add item

Hi all

I have 2 combobox where the items are being autopopulated from 2 different lookup tables

At runtime if I view items in combo1 everything ok
If I view items in combo2 it has items from combo1 and not from specific lookup

Close form
At runtime if I view items in combo2 everything ok
If I view items in combo1 it has items from combo2 not from specific lookup

Which ever Combobox I select first it populates theother combobox with the same item data.

The code below
Any help highly apprecaited
Shaz

Private Sub Form_Load()
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")

Dim i As Integer
Dim TotalCellCount As Integer
Dim Selectcell As String


Set xlbook = xlApp.Workbooks.Open("c:\SIC Look Up Table.xls")


Set xlsheet = xlbook.Worksheets(1)


TotalCellCount = ActiveSheet.Range("A1").CurrentRegion.Rows.Count


For i = 1 To TotalCellCount

Range("A" & i).Select
Selectcell = Application.ActiveCell.Value
Combo1.AddItem Selectcell
Next i

Set xlbook = xlApp.Workbooks.Open("c:\SIC Look Up 4.xls")

Set xlsheet = xlbook.Worksheets(1)
TotalCellCount1 = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
For p = 1 To TotalCellCount1

Range("A" & p).Select
Selectcell1 = Application.ActiveCell.Value
combo2.AddItem Selectcell1
Next p

Set xlApp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

Shezad AhmedAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Alok-AgarwalConnect With a Mentor Commented:
Hi,

Before setting xlbook to  "SIC Look Up 4.xls" ensure that you have closed the workbook by using


Workbooks.close

Hope that helps.

Thanks
Alok
0
 
crazymanCommented:
Any code in your combo events eg:Click,Change etc..
0
 
crazymanCommented:
Also
What is Range and Application
Instead of Application.ActiveCell.Value should it be xlApp.ActiveCell.Value

?
0
 
Shezad AhmedAuthor Commented:
Hi Crazyman

If I change Application.ActiveCell.Value to  xlApp.ActiveCell.Value then  combo1 only fills with the first item in row a.
Range just moves down coulmn A and replaces i with the next number.

What I have noticed is a number of instances of excel.exe running. Could this be the cause. Maybe locking the excel spreadsheet.
Surely there should be only one instance running

I do not have any Click,Change etc..

Cheers
Shezad
0
 
Shezad AhmedAuthor Commented:
Thanks everyone
I placed workbooks.close before setting  "SIC Look Up 4.xls
And also I placed workbooks.close and xlapp.quit before I set to nothing

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.