Combo box row source in an userform

[mariec_06]

In an userform I have a combobox "STR1" for which I try to set the list source.....
The list will depend on another combobox "FLD", but the list will be generated from the worksheet "RECAP" range already named "month" (a2:a142).

I don't manage to refer to the worksheet range....

here below my code

[CODE]
With STR1
If FLD.Value = "MONTH" Then
.additem = "'RECAP'!A2:A142"
end with
[CODE]

Moreover although I want to be able to pick a value from the list in the combobox STR1, I want also to be able to type any value even if not in the list....

many thanks in advance.

[BadlySpelledBuoy]

Try:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

BSB

[mariec_06]

wouah works perfect, I though I had to do this under userform initialized

if the rowsource is not named but it's for example column "AG" of worksheet "recap"....how should I put it?

[mariec_06]

My columns in each worksheet have headers ....if it can help

[BadlySpelledBuoy]

Do you mean you want to refer to a range of cells in column AG specifically or are you trying to make the rowsource dynamic based on the value in the other combobox?

BSB

[mariec_06]

Hum what I am trying trying to make the rowsource dynamic based on the value in the other combobox, considering that the box "FLD" change

so

If FLD=month then STR1.rowsource = worksheets ("recap"). column A
if FLD= "invoicing quantity (p)" then STR1.rowsource = worksheets ("pricing final"). column M

[BadlySpelledBuoy]

Why not give the ranges names and refer to them in the code?
If it's because the ranges could vary in size as data is added/removed, you could make the named ranges dynamic so they resize automatically.

BSB

[mariec_06]

each worksheet (which are repeating some info one from the other) are all preformatted and the range to consider are always row2:row142 (although until now only the row 2 to 31 are filled in but new records are added regularly but my range is 2:142)

my problem in VBA is that I never know how to refer to a column or a row in a worksheet (I never study VBA) and plus as per what I saw when defining a rowsource the way to refer to worksheet is different....

for the moment I can not name each range that's why I would need to define the column by the worksheet name and the column number or letter...

I tried rowsource = "'RECAP'!A2:A142" but it obviously does not work

[bakerman2]

Never use AddItem or Rowsource to fill a Combobox, use the List method instead.

[BadlySpelledBuoy]

I tried rowsource = "'RECAP'!A2:A142" but it obviously does not work

That approach works here in the attached.

BSB

[BadlySpelledBuoy]

Never use AddItem or Rowsource to fill a Combobox, use the List method instead.

Perhaps an explanation of why to use List over the other methods would be helpful.

BSB

[bakerman2]

1. AddItem ==> reading line by line is never a good idea (slow, too much Sheet interaction)
2. RowSource ==> fixed range to be decided in advance.
3. List ==> much more flexible because you can use SpecialCells, Currentregion, Resize, End(xlUp), etc. to read in dynamic range in one go.

[mariec_06]

I tried

Please Login or Register  to view this content.

it does not work, I got an "invalid property value"

[BadlySpelledBuoy]

I think to diagnose that we will need to see the workbook. Could you attach it?

BSB

[mariec_06]

apparently it could be because I renamed my combobox.....

[BadlySpelledBuoy]

apparently it could be because I renamed my combobox.....

Hahaha, that'll do it!

BSB

[mariec_06]

Here attached the file, the userform is the "test" one (don't look at the rest of the code I am working on it), the issue is for the moment under "Fld_afterupdate"

[mariec_06]

here attached the file, the userform is "test" one and the issue is under FLD_afterupdate (don't look at the rest of the code I am working on it)

[mariec_06]

SOLUTION FOUND

Please Login or Register  to view this content.

[mariec_06]

Don't know what was wrong but now it works

thanks to all

[BadlySpelledBuoy]

Glad you've got your solution

BSB

[mariec_06]

Well actually it works good but it does not allow me to enter any value if not in the source.....

I would like to be able to pick a value from the row source or to type any value I want....

Moreover I have an issue when it concerns dates....like if FLD= "B/L date", I would like to be able to type any date value or to pick one of the existing one.........Another issue is that I don't know in the code, where to set the format of the boxes.......

Here attached the working file ( the filter works only on the first worksheet for the moment) but it can be useful to some maybe