help with little vba

[excelworker100]

Hi all...

I have little problem, I hope someone can help me.

I am making comboboxes or listboxes it doesnt really matter that. ANyway

=OFFSET(Countries!$G$1,MATCH(A7,Countries!$G$1:$G$31914,0)-1,1,COUNTIF(Countries!$G$1:$G$31914,A7),1)

How this formula would be in VBA for comboboxes or listboxes or whatever?

Thanks in advance

 

[Claus Busch]

Hi,

Am Sat, 18 Oct 2014 05:18:51 -0700 (PDT) schrieb
(e-mail address removed):

=OFFSET(Countries!$G$1,MATCH(A7,Countries!$G$1:$G$31914,0)-1,1,COUNTIF(Countries!$G$1:$G$31914,A7),1)

create a name with this formula, e.g. myList, insert an ActiveX-ComboBox
and insert in the properties window of the combobox at ListFillRange the
name
ListFillRange: myList


Regards
Claus B.

 

[excelworker100]

I didnt think of this at all... Actually I didnt know that formulas can be in named range.

Thanks for this.

Also one more question. Is there a way to show list before typing in combobox. For example, it is empty before typing. But when I start typing list is there.

 

[Claus Busch]

Hi,

Am Sat, 18 Oct 2014 06:53:56 -0700 (PDT) schrieb
(e-mail address removed):

Also one more question. Is there a way to show list before typing in combobox. For example, it is empty before typing. But when I start typing list is there.

I don't understand.
Where do you want to write? You do not have to write the list to the
combobox. You only have to right click => Properties and insert the name
of the range in the field right of "ListFillRange".


Regards
Claus B.

 

[excelworker100]

Dana subota, 18. listopada 2014. 16:08:37 UTC+2, korisnik Claus Busch napisao je:

Hi,

Am Sat, 18 Oct 2014 06:53:56 -0700 (PDT) schrieb
(e-mail address removed):


I don't understand.
Where do you want to write? You do not have to write the list to the
combobox. You only have to right click => Properties and insert the name
of the range in the field right of "ListFillRange".


Regards
Claus B.

Hi sorry for late reply.

I mean before writing to combobox, that list is there already. So you dont need to write nothing you can choose from list. Now you need to write something so list pops out

 

[excelworker100]

I didnt said thanks before I think... Anyway thanks for help.

And one more thing, I just solve it with GotFocus in VBA instead of change.