Solved: Reloading a combobox with found data

[Rob342]

Hi
I have a Combo box that is loaded with data from a defined field, i need to change it by

1 looking at col CZ and if the value ="J" then reload all the jobnos from col E
2 i created an array to hold all the job nos found from col E and reloaded the combo box, but when selecting a job no from the dropdown it couldn't find the no.... as if it had lost the index?
3 tried attaching the act row as an index, but it didn't like the code
is there a better or quicker way to reload the data keeping the index
Rob

[Rob342]

have attached a same example if anyone would like to take a look, it would be most appreciated

[GTO]

Greetings Rob,

I am not following exactly, but let's see if we can clarify a bit.

I have a Combo box... i need to change it by

...looking at col CZ and if the value ="J" then reload all the jobnos from col E...

You appear to want to load the combo box upon pressing the command button, right?

If so, what or when (or why) would we need to reload?

...i created an array to hold all the job nos found from col E and reloaded the combo box, but when selecting a job no from the dropdown it couldn't find the no.... as if it had lost the index?

What do you mean by Index? That is, we appear to want to later be able to pick a job number (or some Index that would refer to what row the job number is in) and return data from another column (or columns) for that record; is that correct?

Also - at first blush, there are a couple of things that would seem a bit off to me.
[VBA] IRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1
[/VBA]
If we want to return the row number of the last row with data, how about skipping the .Offset(1) and the -1 at the end? Is this just in case there's nothing in the range (the column) except the header?

[VBA] For Each FoundCell In FoundCells

ActRow = Val(Mid(FoundCell.Address, 5, Len(FoundCell.Address)))

ReDim Preserve Job2(TotNo)

Job2(i) = ws.Range("E" & ActRow).Value

i = i + 1

Next FoundCell
[/VBA]

Two things pop out at me. First, you have the resize and preserving of the array in the middle of a loop, whilst the Long variable 'TotNo' has already been assigned a non-changing value. The second is that Job2() is a zero-based array, and you later loop...
[VBA] For C = 1 To TotNo
Me.CboJob2.AddItem Job2(C)
Next C[/VBA]
...from 1 to n , thus missing the first element I believe.

Hope that helps a little at least,

Mark

[Aflatoon]

Listindex will return the position in the combobox list, not the position in the worksheet. I'm not clear either on what you are doing - your code appears to simply put the combobox value into a textbox, but I would suggest that you store the row number when you load the list and then use that whenever you need to refer to a value on the sheet.

[snb]

The only code you need:

[vba]
Private Sub UserForm_Initialize()
CboJob.List = Application.Index(Sheets("AC").Cells(1).CurrentRegion, [row(1:6)], Array(104, 5))
CboJob2.List = CboJob.List
For j = CboJob2.ListCount - 1 To 1 Step -1
If CboJob2.List(j, 0) <> "J" Then CboJob2.RemoveItem j
Next
End Sub

Private Sub CboJob_Change()
TxtDlr.Text = CboJob.List(CboJob.ListIndex, 1)
End Sub
[/vba]

[Rob342]

Thanks aflatoon, snb, Mark for the reply

I am trying to achieve the following
The original data is put onto the sheet via a userform, this records a transaction against a job/claim no in col E and a status of "J" in col CZ
The second part of this transaction is when the user selects a job no to record an invoice and dates against it.
So the thought behind it was as follows
Load the combo box only with the job nos where the status ="J" hence the find all facility.
When the user selects one of the numbers from the combo box, prefill others fields on the form with dates from the sheet from that row, then allow the user to fill in a couple of textboxes with an invoice numer and the date of invoice, then write it all back to the correcct rows and columns, the data starts at row 2 but could be limitless, so need end row in the routine, also periodically there will be no data in there hence the 1 & -1.

i did realise the array was zero based , was trying to incorporate the actrow into an index to get back the the row when updating the sheet, but got lost along the road........

Mark hope this is a bit clearer

Snb will try your routine and come back later
Rob

[Rob342]

snb

Have tried your routine it just fills the combo with J should be filling it with the job/clm nos ie from col E
How can i get it to fill it with job nos, when i change the array around i only get 1 number.

Persuming array(104,5) is looking at col 104 and 5 ?
The finished article will only have 1 dropdown with all the numbers in it that have status J in col 104.

Can you enlighten
Rob

[snb]

see the attachment.

[Rob342]

snb
I see, the column count is set to 2, One last question if the amount of rows is variable ie one time it could be 20 another time it could be 150, how to make this line see that situation?

CboJob.List = Application.Index(Sheets("AC").Cells(1).CurrentRegion,
[row(1:6)], Array(104, 5))

Rob

[snb]

[VBA]Private Sub UserForm_Initialize()
Sheets("AC").Cells(1).CurrentRegion.Name = "snb_001"

CboJob.List = Application.Index([snb_001], [row(snb_001)], Array(104, 5))
CboJob2.List = CboJob.List
For j = CboJob2.ListCount - 1 To 1 Step -1
If CboJob2.List(j, 0) <> "J" Then CboJob2.RemoveItem j
Next
End Sub
[/VBA]

[Rob342]

Hi snb
Thats works a treat, thanks for your help most appreciated
Rob