Blank listing using .additem

[philfloyduk]

Hi.

I am trying to use the .additem function for a combobox on a form I use to search our stock but it is giving me blank results.

For example, I will put a particular make and model in one combobox which has a search command button next to it. The command button locates the correct item on a loop and counts each result under (DIM) SR until it's found them all. On each succesful find I have used:

cbStockIDResults.AddItem Cells(rw, 1)

When expanding cbStockIDResults the list has gone from one blank result before the search to several but the text is not there. The number of blank results tallys up with the search result count found in DIM SR (which I have displayed using a label).

Whilst I was trying to figure out where it was going wrong I set up a cbStockIDResults_change routine to put the information in to a random textbox to see if information is there and it puts the correct stock number in. I have gone as far as checking that the colour of the text in the properties of the combobox is black but still no luck.

Any ideas?

Any help will be greatly appreciated.

Phil

 

[Andrew Poulsom]

Is the list on the ActiveSheet (the Cells property is unqualified)? What happens with:

cbStockIDResults.AddItem Cells(rw, 1).Value

 

[philfloyduk]

I have just tested another form which uses the same code and was working, the same is now happening with this one. This form has a _change function on the results combobox which populates the rest of the textboxes on the form. Again i get the correct 3 blank list result that matches the DIM RC and when I select one of the 'results' (which is blank), it populates the textboxes correctly.

Phil

 

[philfloyduk]

Is the list on the ActiveSheet (the Cells property is unqualified)? What happens with:

cbStockIDResults.AddItem Cells(rw, 1).Value

The sheet is not active, is this where the problem lies? I have just tried activating the relevant sheet then running the form and it works, but this won't be the case when it's being used. I have pasted some of the code below, I had assumed the with sheets("new stock") would have dealt with that?

With Sheets("new stock").Range("c5:c2000")
Set cell = .Find(cbApplianceType.Value, LookIn:=xlValues)
If Not cell Is Nothing Then
foundAddress = cell.Address

Do
'build up the message box text

rw = cell.Row

cbMakeResults.AddItem Cells(rw, 2).Value

'cbStockIDResults.AddItem Cells(rw, 1)
rc = rc + 1
Set cell = .FindNext(cell)

Loop While Not cell Is Nothing And cell.Address <> foundAddress

Many thanks for your reply

Phil

 

[philfloyduk]

I think I've sorted it now, pretty silly thing to miss!

cbMakeResults.AddItem Sheets("new stock").Cells(rw, 2).Value

The times the other one worked must have been when I had the sheet active.

Many thanks again

Phil

 

[Andrew Poulsom]

If your original statement was inside a With...End With construct you could have just added a dot qualifier:

cbStockIDResults.AddItem .Cells(rw, 1).Value

 

[philfloyduk]

Of course!

Thanks

Phil